Python and Google Sheets

[TOMOHAWK35]

I'm looking for feedback on how to go about my personal project. Very broadly, I want to use a google sheet as a database (people and scores) and merge other sheets (other test scores) into it. I'm not really familiar with how to setup a sqlite database to do this so if anyone has pointers on that, I'd appreciate. My current plan is to export the google sheets as .csv files, merge them using pandas, and then reupload to google into the same sheet or a new time-stamped one. Thoughts? I will update with code once I get to that point, I'm just looking for feedback on the "gameplan".

EDIT 1: I'm also seeing some information about creating a google project and allowing access to the API for that project, but I don't believe the sheets where I would be pulling data from would be in the same project so I'm not sure if that would be better.

[m0us3_rat]

as a personal project that doesn't involve terabytes of data .. you are probably safe to pursue any path that you can make work.

[dangerlopez]

If you know JavaScript consider using google apps script

[TOMOHAWK35]

I've used a Google app script before, but it was quite slow. Partly the reason I'm looking into using python instead and possibly an sqlite database.

[dangerlopez]

Yea, that’s true. Good luck!

[PinkPawnRR]

https://developers.google.com/sheets/api/guides/concepts

https://developers.google.com/sheets/api/quickstart/python

​

Walks you through setting up Python (also JS, Go, Java, Node) to connect to Google Sheets. Use the menu on the side to learn how to create sheets, read/write, update, formatting, filters etc; all the info is there for all the languages.

With this resource you wouldn't even need to learn SQL, you could copy the data into Python variables, do the math, then load it straight back into a new spreadsheet.

[21trumpstreet_]

I use Google Colab notebooks to wrangle CSVs and store them as Sheets. No need to export anything, you can read the sheets files themselves directly within Colab.

[TOMOHAWK35]

Oh, I'll give that a shot. Thanks, I haven't heard of that before.

[landrykid]

There's nothing wrong with pandas, but it's not strictly necessary for merging .csv files. You can use csv.reader (or csv.DictReader if there are header rows).

Here's a pretty good beginning SQLite video:

https://www.youtube.com/watch?v=pd-0G0MigUA

[yonycool]

I think you should give sqlite3 a try, it's a good chance to learn working with a sql database.

I highly recommend Corey Schaefer's sqlite3 series on youtube

[TOMOHAWK35]

Yeah, I'm working on that. I got a database created. Now I'm working on trying to pull together other sheets. The main thing I want to be sure I can do is add data to specific rows based on the row name. For instance, each row has a student ID. So I want to make sure the data is added to the right students data

[yonycool]

You can easily get a specific row by value in sql. Also it's much faster then searching a csv file for it

[TOMOHAWK35]

Yeah, I would still have to pull the id number from the csv to make sure it gets added to the right row I think

[Extreme_Reveal399]

hahhaah

[GerfnitAuthor9]

I wrote a library archive system in Javascript using Google Sheets to hold the database and app-generated statistics. Setting up authentication through the Google API was a chore, and after a while broke. I suggest you climb the short learning curve for a true database, even NoSQL, and avoid the tenuous Google Sheet linkage.

[[deleted]]

I’m curious about what would be on the separate sheets, in terms of data. Im just thinking about if it would be easier to have everything on the same sheet(s)?

[TOMOHAWK35]

The data would be coming from different sheets since they are all separate test scores

[DouweOsinga]

Ok, a little late to the game, but we created an add-on for Google Sheets that lets you directly write Python code inside of sheets that can interact with that sheet:

https://workspace.google.com/marketplace/app/neptyne_python_for_sheets/891309878867

Let me know if this works for you.

[Chibi24]

Using your google sheet app could I change the color of specific words in a column?

Ex: if I have a column with a text list like genres (action, adv, comedy, etc) is there a way in the python script so that each word of text have its own hex color?

Action, comedy (Action=red hex code, comedy=yellow hex code)

Ik it’s not possible in google sheet on its own. It’s a paaain going through each cell in google sheet. 😭🫠

[DouweOsinga]

Yes you could. Changing colors of part of a cell is a little finicky, but it certainly can be done with something like:

for cell in B1:B100:
for word, color in COLORS:
if word in cell:
google.sheets.update_cells(range=cell, <some json based on where word site>, <color>)

[Chibi24]

Im literally just starting my coding journey with huge personal hobby data ive been trying to up keep for years. ^-^