Trigger python code from Google spreadsheets?

Question

In excel you can create user defined functions with python using pyxll. I have been moving to Google spreadsheets and using their Google app script, but the libraries are so much bigger and better in python, I wish there was a way to build user defined functions using python from Google spreadsheets. There are ways to interact python with Google sheets like gspread. Is there a way to run python on Google app engine then get sheet to trigger that code? What other ways is there to trigger python code from Google spreadsheets?

Mensuration

Answer 1

One way is to have some code that reads the spreadsheet all the time, then runs some other code when a condition is met.

Without GAE, you could use the following code:

#http://code.google.com/p/gdata-python-client/downloads/list
import gdata.spreadsheet.service as s

spreadsheet_key = 'spreadsheetkey'# https://docs.google.com/spreadsheet/ccc?key=<spreadsheet key>&usp=sharing#gid=0

worksheet_key = 'od6' #first tab
gd_client = s.SpreadsheetsService(spreadsheet_key, worksheet_key)
gd_client.email = '[email protected]'
gd_client.password = 'password'
gd_client.ProgrammaticLogin()
list_feed = gd_client.GetListFeed(spreadsheet_key, worksheet_key)
for entry in list_feed.entry:
    #read cell values and then do something if the condition is met

If you wanted to have the spreadsheet run code in a GAE app, then you could publish the spreadsheet and construct the URL of the spreadsheet (JSON) like this: https://spreadsheets.google.com/feeds/list/(spreadsheetkey)/od6/public/values?alt=json This address can be accessed via the app, the cell values can be read, and some code can be triggered.

The approach is the same with both ideas: some code monitors the spreadsheet and when some condition is met, some other code is triggered. I'm not sure how you could run the code (in a GAE app, say) when the condition is met purely from the Google Spreadsheet.

Yentai

Answer 2

You should create a webservice in GAE which then can be called using Google Apps Script UrlFetch class. This is how I usually do to integrate a third party app with Apps Script App.

In a Spreadsheet container script you can create a code like

function myFunction(){
  //your code
  //Call the webservice
 var response = UrlFetchApp.fetch('my_webservice_url', {payload:'...', method:'POST'});
 Logger.log(response.getContentText());
 // your code based on response
}

Above code can be triggered by a time driven trigger in Apps Script based on some conditions

Hapsburg

Answer 3

Deploy your python code as a cloud function: https://cloud.google.com/functions/docs/writing/http.

Then call your function with URL Fetch as shown above.

Giacinta

saved my day! Took literally less than 5 min. Just be careful to not accidentally do: pip install google-cloud-storage. (Which is in the first steps guide). This made an error while deploying to gcloud. – Academician

Answer 4

YOU CAN USE APP SCRYPT TO SAVE A FILE IN GOOGLE DRIVE FOLDER Upload file to my google drive with Google Apps Script

and use python to watch a folder in you computer with google drive desktop sync with this folder Use Google Drive Desktop to syncfolder of drive in you computer and Use Watch Python to observe this folder

Darrin