CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jordan
Jordan

Reputation: 13

Find the ID of the attached spreadsheet from doGet function

I am looking to have 'openById' find the ID of the spreadsheet it is attached to automatically, if this is possible?

Currently I am pulling cells from a spreadsheet into an HTML template which populates the design with the cell data.

If a user 'makes a copy' of the spreadsheet, the ID (which I have entered manually) is still that of the original spreadsheet I am using, not the new spreadsheet ID of the one they are using.

Would it be possible to get the ID of the spreadsheet that the script is attached with dynamically?

// code.gs
function doGet() {
  var template = HtmlService.createTemplateFromFile('index.html')
  template.sheet = SpreadsheetApp.openById('THE SPREADSHEET ID');

  // returning .evaluate() (an HtmlOutput object) will allow you to display this in a web app.
  // to get the string HTML content, use .getContent() on the HtmlOutput
  return template
      .evaluate();
}

Upvotes: 1

Views: 707

Answers (1)

user3717023
user3717023

Reputation:

The method openById requires an Id, it does not return it. One can get an id with getId called on a spreadsheet object. However, doGet and doPost functions don't have a concept of active spreadsheet; the method SpreadsheetApp.getActiveSpreadsheet() returns null when called from them. It seems that Web Apps are never considered bound to a spreadsheet, as documentation hints at when listing triggers.

So, there is no direct way to achieve what you want. But there is a workaround: instruct the user to execute a function capturing Id and storing it in ScriptProperties (they'll need to authorize this, so onOpen won't do). Example: 

function recordId() {
  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  PropertiesService.getScriptProperties().setProperty('id', ssId);
}

function doGet(e) {
  var id = PropertiesService.getScriptProperties().getProperty('id');
  var ss = SpreadsheetApp.openById(id); // have access to spreadsheet
  return ContentService.createTextOutput(id);  // confirms that id is known to the script
}

You can make the process easier by using onOpen to create a menu item that will launch recordId.

Upvotes: 1

Related Questions