从doGet函数中查找附加电子表格的ID [英] Find the ID of the attached spreadsheet from doGet function

问题描述

我希望'openById'找到自动附加的电子表格的ID（如果可能的话）？

目前我正在从电子表格转换成HTML模板，用单元格数据填充设计。

如果用户'复制'电子表格的ID（我手动输入）仍然是我使用的原始电子表格，而不是他们正在使用的新电子表格ID。

是否可以获取电子表格的ID该脚本是动态附加的？

  // code.gs 
函数doGet（）{
 var template = HtmlService.createTemplateFromFile（'index.html'）
 template.sheet = SpreadsheetApp.openById（'THE SPREADSHEET ID'）; 
 
 //返回.evaluate（）（一个HtmlOutput对象）将允许您在Web应用程序中显示它。 
 //获取字符串HTML内容，在HtmlOutput 
返回模板中使用.getContent（）
 .evaluate（）; 
 
  

解决方案 openById 需要一个Id，它不会返回它。可以在电子表格对象上调用 getId 标识。但是， doGet 和 doPost 函数没有活动电子表格的概念;方法 SpreadsheetApp.getActiveSpreadsheet（）从它们调用时返回 null 。似乎Web应用程序永远不会被视为绑定到电子表格，因为文档提示列出触发器时。

所以，没有直接的方法来达到你想要的。但是有一个解决方法：指导用户执行一个捕获Id的函数并将其存储在ScriptProperties中（他们需要授权这个，所以 onOpen 不会这样做）。例如：

pre $函数recordId（）{
var ssId = SpreadsheetApp.getActiveSpreadsheet（）。getId（）;
PropertiesService.getScriptProperties（）。setProperty（'id'，ssId）;


函数doGet（e）{
var id = PropertiesService.getScriptProperties（）。getProperty（'id'）;
var ss = SpreadsheetApp.openById（id）; //有权访问电子表格
返回ContentService.createTextOutput（id）; //确认该脚本已知脚本
}

您可以使该过程更轻松通过使用 onOpen 创建一个将启动recordId的菜单项。

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();
}

解决方案

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.

这篇关于从doGet函数中查找附加电子表格的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！