Personally i feel more confortable making a script in python and there are more libraries to use than a macro. But if there is no option.. Thank you in advance.
4
-
developers.google.com/sheets/apiAMC– AMC2020-02-22 19:01:29 +00:00Commented Feb 22, 2020 at 19:01
-
1I now how to connect python with google spreadsheet, but not the other way around. I want if you click a button in google spreadsheet, run the python script.castel594– castel5942020-02-23 20:22:32 +00:00Commented Feb 23, 2020 at 20:22
-
Well do you know if that’s possible?AMC– AMC2020-02-23 20:29:18 +00:00Commented Feb 23, 2020 at 20:29
-
Authentication is a bit of a pain when interacting with python. To some degree you can work around this by using colab, but it's still anothing thing to use.Att Righ– Att Righ2022-06-28 10:26:45 +00:00Commented Jun 28, 2022 at 10:26
Add a comment
|
2 Answers
Your issue can be solved by using an onOpen(e) trigger and by hosting your python script on Google Cloud.
1. Use the onOpen(e) trigger
The onOpen(e) trigger is used to create the menu MY MENU in your Spreadsheet each time the Spreadsheet opens. Moreover, the submenu item TRIGGER THE PYTHON SCRIPT will have the function runScript() associated with it and each time you click it, that function will be run.
The above behavior can be achieved by using this snippet in Apps Script
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('MY MENU')
.addItem('TRIGGER THE PYTHON SCRIPT', 'runScript')
.addToUi();
}
2. Host your Python script on Google Cloud
You should host your Python Script in Google Cloud as a Cloud Function and run the code.
The runScript() is the function triggered by TRIGGER THE PYTHON SCRIPT menu from above and the snippet looks something like this.
function runScript() {
var params = {
'method': 'post',
'headers': {
'contentType': 'application/json',
'payload': '{"name":"Name"}'
}
};
var pyScript = UrlFetchApp.fetch('https://YOUR_REGION-YOUR_PROJECT_ID.cloudfunctions.net/FUNCTION_NAME', params);
}
Note: You might have to change the params based on your script and your desired actions.
Reference
Comments
I guess you could run a script yourselves and communicate with the spreadsheet through the API, but as far as I know, you have to use Apps Script to run scripts in the spreadsheet.
Comments
