How can I set an app script on a Google Sheet via the API?

Question

Is there a way to set an App Script on a new Google Sheet via the Google Sheets API? We use pygsheets as our Python API Client for Google Sheets. Since we are using the onSelectionChange trigger to populate certain cells with a date, we would like to setup the app script after we have populated rows in the spreadsheet.

Answer 1

The Google Sheets API doesn't have a method to do this. You can use the Apps Script API instead, but there are a few caveats which may or may not work with your use case:

• You can use projects.create to create a new Apps Script project and attach it to a Sheets file by specifying the parentId field, which is the Sheet's ID. However, there are no methods to attach a preexisting script to a Sheet. You can still use projects.updateContent to add the required script files, though.
• You cannot unbind the script once it has been attached.
• This API does not work with service accounts, and the user running it needs to explicitly allow it in their settings page. This is for security reasons, since allowing a malicious app to freely write scripts on your account could be catastrophic, but it adds more steps that your users would need to take in order to run your app.