How to use Google Advanced Services Sheets to generate multiple charts?

[Ludovic Wagner]

I would like to build several charts using the Sheets advanced service (I'm not so sure but I think this service gives access to the API the same way it can be used from external languages).

I see that the the first example on this page Charts looks like

{
  "requests": [
    {
      "addChart": {
...
    }
  ]
}

I believe the requests can contain several addChart as well as other "entities".

For the time being, I got it working with only one entity (TypeScript syntax).

 var request : GoogleAppsScript.Sheets.Schema.Request = Sheets.newRequest();
 
 request = {
   'addChart': {
     ..
   }
 };

var batchUpdateRequest : GoogleAppsScript.Sheets.Schema.BatchUpdateSpreadsheetRequest = Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateRequest.requests = request; // claps underlines this instruction..

return batchUpdateRequest;

I can't find find type which can handle multiple requests (i.e. for the first part of code) and I don't think that GoogleAppsScript.Sheets.Schema.BatchUpdateValuesRequest will help.

Does anyone have an idea how to achieve it?

Thanks for any insights :-)

[ludovic....@gmail.com]

Well, I found where the issue was. In fact,  batchUpdateRequest.requests  expects an array which I initialize to Array(<number>) and then have to add each request as batchRequests.requests[<ind>] = request...

Nevertheless, I realize that every time I push the code via claps, I have to enable Sheets (API) in the script editor resource. Is there a way to automate it? (I thought about adding a scope in the appscript.json manifest, but don't think there is any...)

[Alex]

You have to enable Sheets API Advanced Service every time for a new project.

[Martin Hawksey]

Advanced services can also be enabled in the manifest

  "dependencies": {

    "enabledAdvancedServices": [

      {

        "userSymbol": "Sheets",

        "version": "v4",

        "serviceId": "sheets"

      }

    ]

  }

Possible that clasp is overwriting your appsscript.json disabling the service each time you push?

[Ludovic Wagner]

OK, thanks Martin. It works perfectly well.

In fact, I had nothing specified in the dependencies section in my manifest file. Seems like I still need to spend more time reading the documentation..

[Martin Hawksey]

Using the online IDE the manifest is updated automatically, so using clasp it can easily be missed ... it's one of those numerous Apps Script behaviours that can hard to discover even through the documentation (why communities like this are useful :)