0

I have successfully created a client side script function that removes and reinstates all conditional formatting on a templated Sheet (to maintain a prescribed conditional formatting regime without locking cells). I have placed a call to this function in the sheets onOpen() event handler. When in the Script Editor I explicitly run the onOpen handler all works well. When I open the sheet and let the onOpen event run naturally I get an authentication error in response to the batchUpdate call that requests the Conditional Formatting changes:

Execution failed: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential.

1) Is there a logical explanation why the code does not fail authentication when I run the code explicitly from the Script Editor while it does fail in response to the onOpen event firing?

2) I have followed the OAuth 2 links and am a little overwhelmed, can anybody please direct me to a simple set of actions I need to take to allow me to make a client side script work with the Sheets object to which it is attached for all users? Is this a more difficult endeavour than I had anticipated?

Update: I got my code to work in script attached to a spreadsheet. An installable trigger was used to send API batch update requests of Conditional formatting - all good so far. I have a number of spreadsheets that I want to use the script, so I created a version of the script and added it as library to the first "new"spreadsheet. I had to authorize the API in that new sheet but once done the library trigger event fired at the appropriate point. However, despite the API batch update request being written in the context of an installable trigger (in the library file) execution again failed with the original error message:

"Execution failed: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential message.

Precisely the problem the Installable Trigger had solved previously.

What extra steps must I take to avoid this error in a library trigger please?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Malcolm Farrelle
  • 177
  • 14
  • with massive thanks to Antoine Colson, Installable Triggers seem to offer a way forward. If instead of having the onOpen event call the code that issues the conditinal formatting batch update I add an installable trigger which calls that code and which is triggered by the onOpen event all seems well. Here's the thread: https://stackoverflow.com/questions/47735627/api-works-when-running-script-but-not-from-associated-spreadsheet – Malcolm Farrelle Feb 15 '18 at 14:58
  • Frustratingly it now looks like it is not going to be possible to maintain conditional formatting on a sheet using code. Because API write requests are required to reinstate conditional formatting and quotas apply (https://developers.google.com/analytics/devguides/config/provisioning/v3/limits-quotas) it is not possible to ensure that every change by a user to conditional formatting can be trapped and backed-out. At best, a batch request run once per day per project which deletes and reinstates conditional formatting will ensure the conditional formatting is correct at least once each day. – Malcolm Farrelle Feb 21 '18 at 19:35
  • 1
    You can now use native Apps Script to modify conditional format, without needing to use the Sheets REST API. This won't solve your requirement to use an installed trigger, but will mean that authentication will be much much simpler (since you will be using an authenticated `SpreadsheetApp` directly) https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule – tehhowch Apr 12 '18 at 18:39

0 Answers0