Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.4k views
in Technique[技术] by (71.8m points)

google apps script - 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();
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...