CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
CNM
CNM

Reputation: 31

"Service Spreadsheets failed" error when calling openById method in a standalone script project

I've been trying to access spreadsheets from a standalone google script, but keep getting the following error message:

Exception: Service Spreadsheets failed while accessing document with id abcdefg_example_id. (Code:25:33)

function doSomething() {
  var theSheet = SpreadsheetApp.openById("abcdefg_example_id");
  if(theSheet == null){
    Logger.log("NO SPREADSHEET");
  } else {
    Logger.log("SPREADSHEET REEE");
  }
  Logger.log(theSheet.getName());
}

How do I access a spreadsheet by ID from a standalone google script?

Also, I've tried running the same code within a container bound script and it won't work when trying to open a different spreadsheet. Same error message. My goal is basically to access and modify other spreadsheets ideally from a standalone script but I don't mind if it has to be done another way.

I tried the same code on my PC using personal files and it works. I'm trying to get this code to work on a work computer but it runs into the issue above. I'm guessing maybe it's an authorization problem or perhaps an administrator restriction of some sort. Any ideas?

Upvotes: 3

Views: 1318

Answers (2)

Bruno Couthenx
Bruno Couthenx

Reputation: 21

Have you checked if the spreadsheet you're trying to "openById" is in a Google Doc format(has been converted when uploaded to Gdrive)? if it is not, google app script will throw this error.

Upvotes: 2

Cooper
Cooper

Reputation: 64042

I just did this:

function getDataFromAnotherSpreadsheet() {
  const ss=SpreadsheetApp.openById("ssid");
  const sh=ss.getSheetByName('Sheet1');
  const rg=sh.getDataRange();
  const vs=rg.getValues();
  let html="";
  vs.forEach(function(r,i){
    html+=r.join(',') + '<br />';
  });
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), "Results")
}

My Output:

COL12,A1,COL10,B1,COL8,C1,COL6,D1,COL4,E1,COL2
133,1,109,1,85,1,61,1,37,1,13
134,2,110,2,86,2,62,2,38,2,14
135,3,111,3,87,3,63,3,39,3,15
136,4,112,4,88,4,64,4,40,4,16
137,5,113,5,89,5,65,5,41,5,17
138,6,114,6,90,6,66,6,42,6,18
139,7,115,7,91,7,67,7,43,7,19
140,8,116,8,92,8,68,8,44,8,20
141,9,117,9,93,9,69,9,45,9,21
.....

Getting ID: (animation)

enter image description here

Upvotes: 1

Related Questions