0

I try to open a remote google spread sheet within a .gs.function like this:

function openOLT35A001T() {
  // Datenbank-Spreadsheet
  const cSSOLT = SpreadsheetApp.openById('16EBPA1TeOoMgn9fBmCJN8LwIMEGEYBSEvvxxxxxx');
  // Tabelle Worksheet
  const cOLT35A001T = cSSOLT.getSheetByName('OLT35A001T');

  return cOLT35A001T;
}// openOLT35A001T

It works within another .gs-function without problems like this:

function lkpRecord_OLT35A001T() {

 const cOLT35A001T = openOLT35A001T(); 

 var vDataRange = cOLT35A001T.getRange("A2:E").getValues();
 Logger.log("vDataRange :" + vDataRange);

}//lkpRecord_OLT35A001T

But if i call the function from within a OnEdit-Trigger I get a privilege error:

function onEdit(e) {
 if(e.source.getSheetName() === "FRM35A001T" && e.range.getA1Notation() === "C5") {
  lkpRecord_OLT35A001T();
 }

Exception: Sie sind nicht berechtigt, SpreadsheetApp.openById anzurufen. Erforderliche Berechtigungen: https://www.googleapis.com/auth/spreadsheets
    at openOLT35A001T(FRM35A001T:13:33)
    at lkpRecord_OLT35A001T(FRM35A001T:45:22)
    at onEdit(Code:7:3)

Why is that and how to solve?

The same behavior i get, if I put the SpreadsheetApp.openById outside a function, let's say as a global constant.

idfurw
  • 5,727
  • 2
  • 5
  • 18
Jaimi
  • 23
  • 4
  • 1
    I thought that these threads might be the answer for your question. https://stackoverflow.com/q/16738298 https://stackoverflow.com/q/27744929 – Tanaike Dec 13 '21 at 05:15
  • Try changing the name of the onEdit() function to something like onMyEdit() and the create an installable trigger with onMyEdit as the handlerfunction and try it again. – Cooper Dec 13 '21 at 06:00

1 Answers1

0

It would be better to write your function like this:

function lkpRecord_OLT35A001T() {
  const cOLT35A001T = openOLT35A001T();
  var vDataRange = cOLT35A001T.getRange(2,5,cOLT35A001T.getLastRow() - 1,5).getValues();
  Logger.log("vDataRange :" + vDataRange);
}

Using an indeterminate range with A1 notation always leads to nulls the the of the data set.

halfer
  • 19,824
  • 17
  • 99
  • 186
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • @halfer I don't understand I can't find `"the function does do much else"`. – Cooper Mar 25 '22 at 23:41
  • Thanks Cooper - I'll remove that old comment. It was in reference to an [earlier revision](https://stackoverflow.com/posts/70330524/revisions) (now removed, since the OP hasn't responded to the request for clarification). – halfer Mar 25 '22 at 23:42