2

I have a master spreadsheet which contains a script that makes a POST request to my server onEdit with the current spreadsheet ID.

function post2Server(){
  ss = SpreadsheetApp.getActiveSpreadsheet();

  payload                   = {};
  payload['spreadsheet_id'] = ss.getId();

  headers = {
    'Content-Type': 'application/json',
    'Accept'      : 'application/json'
  }

  options = {
    'method'     : 'post',
    'contentType': 'application/json',
    'headers' : headers,
    'payload' : JSON.stringify(payload)
  }

  res = UrlFetchApp.fetch(MY_SERVER_URL, options);
  return;
}

This function works as intended on the master sheet. Now, when I use the python google API to create a copy of this master sheet, the script copies over, however, doesn't run. I get an error saying....

Server error occurred. Please try saving the project again.

Why isn't this running? Within the copy of the spreadsheet, I even create a new function which simply logs "hello" and receive the same error. It appears that after the Python SDK copied the master sheet, no functions run. Is this a permissions issue? How can I get the script to execute in any subsequent copy of the master sheet?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
yesyoukenn
  • 207
  • 4
  • 17
  • This will be a new project. Try deleting manifest appsscript.json. You need to re-auth – TheMaster Oct 18 '18 at 19:31
  • The purpose is to have this done programatically. We can't ask our users to do this for every copy of the spreadsheet. – yesyoukenn Oct 18 '18 at 19:33
  • Well, First, Does it work manually? If so, You may try `ScriptApp.invalidateAuth()` to re-authorize. – TheMaster Oct 18 '18 at 19:39
  • The script executes correctly on the master sheet, but does not execute on the copied spreadsheet (i.e. the copy was copied by Python SDK). – yesyoukenn Oct 18 '18 at 19:44
  • @yesyoukenn Can you provide the information about the method for trying to run the script? – Tanaike Oct 18 '18 at 22:14
  • Yes, it's in the code snippet above. I am trying to POST the spreadsheet ID of a copied spreadsheet to my server @Tanaike – yesyoukenn Oct 19 '18 at 03:30
  • @yesyoukenn I'm sorry for my poor English skill. I wanted to ask about how you execute the script, because I thought that such information might become a material for resolving the issue. – Tanaike Oct 19 '18 at 09:07
  • Within the Tools > script editor menu from the copied sheet, I select the function and click the "play" button. Seems that no function, even something as simple as logging, executes with the error: 'Server error occurred. Please try saving the project again.' @Tanaike – yesyoukenn Oct 19 '18 at 15:34
  • @yesyoukenn Thank you for replying. I could understand your situation. In my environment, when I run a function of the bound script after I copied the Spreadsheet, the script works fine. I couldn't replicate your situation. For example, when you manually copy the Spreadsheet, the function can be run? – Tanaike Oct 19 '18 at 23:50
  • @Tanaike when I manually copy the sheet it works just fine, however, when I use the Sheets v4 REST API to copy the sheet, none of the bound scripts work. – yesyoukenn Oct 22 '18 at 01:44
  • 2
    @yesyoukenn Thank you for replying. There might be the issue for the method of copy. So can you provide the detail information including the script for copying spreadsheet you are using? If you can do, please update your question. – Tanaike Oct 22 '18 at 01:55
  • You need a different script pattern. You should be using an add on to about needing to authorize every sheet you create, and to avoid creating a new script to maintain with every copy. You can domain-install the add on. Consider also programmatically creating the edit trigger from your master script for the new spreadsheets, and not creating the new sheets with any script attached. – tehhowch Oct 22 '18 at 11:25
  • 1
    You said: `Seems that no function, even something as simple as logging, executes with the error:` Which means, the code in your question is irrelevant. Consider rewriting your question completely with all the information in comments with more research. Please read [mcve] and [ask]. – TheMaster Oct 22 '18 at 11:52

2 Answers2

0

Usually my approach to this kind of requirements is to use the scripts.run method of the Google Apps Script REST API. In this way you are in full control of what scripts are executed and of the parameters.

The main conditions you have to met to use this method are the following:

  • Deploy the script project as an API executable
  • Provide a properly scoped OAuth token for the execution
  • Ensure that the script and the calling application share a common Cloud Platform project

If you look for more details there is a dedicated page in the documentation that explains how to run a specific method of your Apps Script and at the bottom of it you have a sample in many different language, including python.

Lorenzo Persichetti
  • 1,480
  • 15
  • 24
0

Right now copying a sheet that contains a bound Google App Script using the Drive API authenticated through a Google Service Account will always render the script unusable due to a bug in the Drive API (filed here). Until that's fixed, there's no simple solution to this.

In our case, we were able to eliminate our use of the bound script by utilizing functions in Sheets, specifically the IMPORTDATA function as a means of sending web requests.

Jake Magill
  • 56
  • 9