Is there a way to execute google apps script from PostgreSQL or from Bash?

1
votes

I have hundreds of google spreadsheets which all have the same script in them. I'd like to execute a specific google spreadsheet by ID either from PostgreSQL or from Bash. Is there an easy way to execute scripts? I prefer not having to learn python.

For example, I have spreadsheet id 1HGo8V-P5S-z4oCUiAKULrkZRFlzRHEalJqUFLiP and I'd like to execute function Refresh() via Bash or Postgresql.

Is the API the only route? https://developers.google.com/apps-script/api/how-tos/execute

So far my consideration is to create a python executable and execute it from a postgresql function. https://developers.google.com/apps-script/api/quickstart/python

Edit: It looks like if I want to execute any app script, each spreadsheet would need a google project (manually created). There has to be an easier way to do this, manually creating a project for every spreadsheet doesn't make sense.

Reference: https://developers.google.com/apps-script/api/how-tos/execute

The script being called and the calling application must share a Cloud Platform project.

Solution: Deploy web apps is the solution I ended up going with thanks to @Tanaike

1
postgresqlgoogle-apps-scriptgoogle-sheets

1 Answers

1
votes
  • You want to execute the container-bound script of Spreadsheet.
  • You want to execute the script from local PC or outside of Google.
  • You want to execute the script from Spreadsheet ID.

If my understanding is correct, how about this answer?

Retrieving project IDs of container bound script:

I think that this is the difficult problem in your situation. In the current stage, there are no methods for retrieving the project ID of the container-bound script from the parent Google Docs (in this case, it's Spreadsheet.).

  • This has already been reported at https://issuetracker.google.com/issues/111149037 as a Feature Request. But unfortunately, this has not been achieved yet.
  • By this, the project IDs are required to be retrieved manually.
  • For example, you can retrieve the project IDs at Developer Hub.
    • When you select a project, you can see the project ID at the URL of https://script.google.com/home/projects/### projectId ###.

From above situation, the project IDs are required to be manually retrieved from each Spreadsheet you want to run the script. I apologize that I couldn't find a workaround for this situation.

Executing script:

I think that there are 2 patterns for executing the script. You can select from 2 patterns.

1. Use the method of scripts.run in Apps Script API

  • This is also mentioned in your question.
  • Function in the script is execute using API.
  • You are required to create only python script at local side.
  • In this case, the access token retrieved by the OAuth2 process is required at local side.

I think that in this pattern, the script can be simple.

2. Use Web Apps

  • By accessing to Web Apps, function in the script is execute.
    • In this case, in order to execute the script, it uses scripts.run in Apps Script API, because of large number of Spreadsheets in your situation. When user accesses to Web Apps, the script of Spreadsheet is executed by the script of Web Apps. Because Google Apps Script is used, the access token can be easily retrieved.
  • You are required to create a python script for accessing to Web Apps at local side, and Google Apps Script at Google Side (Web Apps).
  • In this case, you can run the script with and without the access token at local side.

I think that in this pattern, the method of access can be simple.

References:

If this was not what you want, I apologize.

Edit:

From the discussions, I thought that in the case of your script Refresh(), the following flow is suitable. 1. Deploy Web Apps using the script of Refresh() like an API. 2. Call the API using python script and curl. By this, the script of Web Apps is run and you can reflect the result of Refresh() for all Spreadsheets.

Sample script:

function doGet() {
  var spreadsheets = ["spreadsheetId1", "spreadsheetId2",,,]; // Please set spreadsheet ID here.
  for (var i = 0; i < spreadsheets.length; i++) {
    SpreadsheetApp.openById(spreadsheets[i])
      .getSheetByName("Sheet1") // If you want to also put values to other sheet, please modify this.
      .getRange(1, 1)
      .setValue(new Date());
  }
  return ContentService.createTextOutput("Done.");
}
  • When Web Apps is deployed, the authorization screen is opened. But this authorization is only one time.

Deploy Web Apps:

Before you request to Web Apps, please deploy Web Apps.

  • On the Script Editor
  • Publish -> Deploy as Web App
    1. Create new Project version
    2. At "Execute the app a"s, select "Me"
    3. At "Who has access to the app", select "Anyone, even anonymous"
    4. Click "Deploy"
    5. Copy "Current web app URL"
    6. Click "OK"

Curl sample:

If you use curl command, please use as follows.

$ curl -L "https://script.google.com/macros/s/#####/exec"

Note:

  • When you modified the script, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. This is an important point.

Note:

  • Also you can use the spreadsheet IDs for requesting and you can directly includ them in Web Apps. (In the sample script, the latter is used.)
    • If you want to use the spreadsheet IDs for requesting, please be careful. The number of workers for Web Apps is required to be less than 30. Ref