0

Following these guides https://developers.google.com/apps-script/guides/rest/quickstart/target-script and https://developers.google.com/apps-script/guides/rest/quickstart/nodejs, I am trying to use the Execution API in node to return some data that are in a Google Spreadsheet.

I have set the script ID to be the Project Key of the Apps Script file. I have also verified that running the function in the Script Editor works successfully.

However, when running the script locally with node, I get this error:

The API returned an error: Error: ScriptError

I have also made sure the script is associated with the project that I use to auth with Google APIs as well.

Does anyone have any suggestion on what I can do to debug/ fix this issue? The error is so generic that I am not sure where to look.

UPDATE: I've included a copy of the code in this JSBin (the year function is the entry point) https://jsbin.com/zanefitasi/edit?js

UPDATE 2: The error seems to be caused by the inclusion of this line var spreadsheet = SpreadsheetApp.open(DriveApp.getFileById(docID));

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
Tri Nguyen
  • 9,950
  • 8
  • 40
  • 72

2 Answers2

4

It seems that I didn't request the right scopes. The nodejs example include 'https://www.googleapis.com/auth/drive', but I also needed to include 'https://www.googleapis.com/auth/spreadsheets' in the SCOPES array. It seems like the error message ScriptError is not very informative here.

In order to find what scopes you'd need, to go the Script Editor > File > Project Properties > Scopes. Remember to delete the old credentials ~/.credentials/old-credential.json so that the script will request a new one.

Tri Nguyen
  • 9,950
  • 8
  • 40
  • 72
0

EDIT: With the update in information I took a closer look and saw you are returning a non-basic type. Specifically you are returning a Sheet Object.

The basic types in Apps Script are similar to the basic types in JavaScript: strings, arrays, objects, numbers and booleans. The Execution API can only take and return values corresponding to these basic types -- more complex Apps Script objects (like a Document or Sheet) cannot be passed by the API.

https://developers.google.com/apps-script/guides/rest/api

In your Account "Class"

this.report = spreadsheet.getSheetByName(data.reportSheet);

old answer:

'data.business_exp' will be null in this context. You need to load the data from somewhere. Every time a script is called a new instance of the script is created. At the end of execution chain it will be destroyed. Any data stored as global objects will be lost. You need to save that data to a permanent location such as the script/user properties, and reloaded on each script execution.

https://developers.google.com/apps-script/reference/properties/

Spencer Easton
  • 5,642
  • 1
  • 16
  • 25
  • I define data in the same file before `year`. Does that not work that way? – Tri Nguyen Feb 29 '16 at 22:41
  • Yes if 'data' is defined "statically" then 'year' will have access to the 'data' object. You didn't show that in the jsbin example. 'error: Error: ScriptError' means there is a bug in the script itself then. – Spencer Easton Mar 01 '16 at 02:23
  • I've updated the jsbin to include what `data` looks like. I am not sure what bug there would be in the script, as running the `year` function locally works. – Tri Nguyen Mar 01 '16 at 04:28
  • Instead of returning `business_exp` in the `year` function, I instead return `business_exp.months`, but still get the ScriptError. I thought if I just return `this.months`, which is an Array, it would not be a non-basic type? – Tri Nguyen Mar 01 '16 at 15:23
  • Take a look at the node.js example: https://developers.google.com/apps-script/guides/rest/quickstart/nodejs. It shows how they do a dump of the entire stack trace when an error is returned. – Spencer Easton Mar 01 '16 at 19:55
  • Yeah, I am using that example. The issue I am seeing is that it didn't even get to the response, that it errors out before that ('The API returned an error'). I think I've narrowed down the error as due to the inclusion of these lines: `var spreadsheet = SpreadsheetApp.open(DriveApp.getFileById(data.docId)); var category = spreadsheet.getSheetByName('Categories');` – Tri Nguyen Mar 01 '16 at 20:10