识别表单目的地(电子表格和工作表) [英] Identifying Form destination (Spreadsheet AND SHEET)

问题描述

我正在编写一个与 Google 表单的响应表交互的脚本.

I'm working on a script that interacts with Google Form' response sheet.

FormApp.getActiveForm().getDestinationId()

给我电子表格 ID，但我找不到获取工作表本身的方法.用户可以更改其名称和位置，因此我需要获取其 id，例如

give me the spreadsheet id, but I don't find a way to get the sheet itself. User can change its name and position, so I need to get its id, like in

Sheet.getSheetId()

我还必须确定响应使用的列数.它不等于表格中的问题数量.我可以计算表格中的项目数:

I also have to determine the number of columns the responses uses. It's not equal to the number of questions in the form. I can count the number of items in the form:

Form.getItems().length

然后搜索gridItems，将每行的行数相加减一:

and then search for gridItems, add the number of rows in each and add them minus one:

+ gridItem.getRows().length - 1

最后，我认为没有办法将每个问题与工作表中的每一列相关联，而是通过以某种方式将列名与项目标题进行比较.

Finally, I think there's no way to relate each question with each column in the sheet, but by comparing somehow columns names with items title.

谢谢

推荐答案

现在有一种方法可以通过使用 Sheet#getFormUrl()，已添加到 2017 中的 Sheet 类.

There is now a way to verify which sheet in a Google Sheets file with multiple linked forms corresponds to the current Form - through the use of Sheet#getFormUrl(), which was added to the Sheet class in 2017.

function getFormResponseSheet_(wkbkId, formUrl) {
  const matches = SpreadsheetApp.openById(wkbkId).getSheets().filter(
    function (sheet) {
      return sheet.getFormUrl() === formUrl;
    });
  return matches[0]; // a `Sheet` or `undefined`
}
function foo() {
  const form = FormApp.getActiveForm();
  const destSheet = getFormResponseSheet_(form.getDestinationId(), form.getPublishedUrl());
  if (!destSheet)
    throw new Error("No sheets in destination with form url '" + form.getPublishedUrl() + "'");
  // do stuff with the linked response destination sheet.
}

如果您取消了表单和目标电子表格的链接，那么显然您将无法使用 getDestinationId 或 getFormUrl.

If you have unlinked the Form and the destination spreadsheet, then obviously you won't be able to use getDestinationId or getFormUrl.

这篇关于识别表单目的地(电子表格和工作表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！