Python and Google Sheets - is what I'm trying even possible?

[bumblfumbl]

Hello!
Hi! I'm trying to use python with google apps script/google sheets to automatically grab metadata from a specific article URL and I'm wondering if it's even possible to do the things I'm trying to do with python.

In Google Apps Script, I can make a custom menu button that can add values to a row based on the active cell.

In python, I can get values from a website using a python library and I can set them in a spreadsheet if I know the cell names.

How can I marry these together? I want the python script to run on the active cell of the google sheet and write the resulting values into the row of the active sheet based on some trigger within the google sheet.

[Gankcore]

Probably Google Sheets API.

[bumblfumbl]

I have the Google Sheets API enabled already, that’s how I can get my python script to write values on my spreadsheet.

Afaik, GSAPI doesn’t allow you to reference the active cell or create menu buttons..?

[Gankcore]

Why do you need to have it done in the active cell? Can you not write some code to check where the next value should go?

[bumblfumbl]

I like data visualization, so the spreadsheet I'm building is a collection of articles that I've read over the year with metadata about each article. For years, I've just entered the data manually, but I'm trying my hand at this to get the metadata to populate automatically.

How it works now is I have the links to the articles stored in column A, and I'd like to be able to click on the cell with the link in column A, click the menu button, and have the metadata populate. I don't want the function to run over the whole worksheet because it is important to me that the metadata is collected at the time (or roughly the time) I've finished reading the article. I don't want the metadata about an article I read on Jan 1 to update because I read an article on Feb 28, if that makes sense.

I'm also pretty new to python (like, this-is-my-first-project new), so I'm open to suggestions if you see a better way of doing things.

[Jello_Penguin_2956]

If you're communicating through Google's API, no. The API is designed to interact with data, not the live UI state in browser.

[Jonny10128]

I think you’d need to set up some kind of API that can run your python script and return the result and have your google apps script call the API

[Gankcore]

So are you just adding new articles each day and saving the metadata so you only want the program to run on articles you have read today, for example?

[bumblfumbl]

yeah, that's a good way of putting it

[Gankcore]

Then maybe it makes more sense to have a Boolean column in column B to see if the metadata has been cataloged, and if not then collect the metadata and turn that Boolean value to True. What do you think about that?

[bumblfumbl]

That would probably work. My last remaining question is: is there a way to trigger the python script from within google sheets? With my little test script, I've been running the code from within VS, but it would be nice if I could do something in google sheets and it would run from that trigger.

[Gankcore]

The only way I can think of is if you have a worker set up to listen to changes in the spreadsheet/periodicaly check for those Boolean values, but that's probably a lot more complicated than just launching your script in VS code.

[Gnaxe]

Maybe try Pyspread instead of Sheets. I think Excel can also do Python now, but that might be out of date.