我的总体目标是每周为表单创建一个新的"响应"表(在同一个电子表格中(。在处理一周的响应时，考虑到表单响应电子表格的限制(无法删除行(，我尝试做以下操作：
// ...
// Copy the existing answers to a new sheet in the spreadsheet and rename it
// with this week's DateTime.
today = Utilities.formatDate(new Date(), 'GMT', 'dd/MM/yyyy HH:mm');
currentSheet.copyTo(spreadsheet).setName(today);
// Delete the form responses from the Form. This will _not_ update the
// responses Spreadsheet.
form = FormApp.openByUrl(spreadsheet.getFormUrl());
form.deleteAllResponses();
// Begin dodgy hack
// First unlink the Form from the responses Spreadsheet
form.removeDestination();
// Delete the old responses Sheet
console.log(`Removing the ${CURRENT_SHEET_NAME} sheet`);
spreadsheet.deleteSheet(currentSheet);
// Relink the existing responses Spreadsheet to the Form
form.setDestination(FormApp.DestinationType.SPREADSHEET, SPREADSHEET_ID);
// It's not possible to get a handle on the sheet the responses will be put
// into from Form or FormApp, so there are two choices: iterate through
// the sheets and find one with the correct FormUrl, or hope that for
// all eternity, Google Apps Scripts will add new sheets at the zeroth index.
const formId = form.getId();
// **MADNESS**: only the renamed sheet comes back from getSheets. In the
// Spreadsheet UI there is now two sheets: `Form Responses N` and `today`
// where N is the ridiculous number of times I've tried to run this script
// and Today is the current DateTime.
spreadsheet.getSheets().forEach((sheet) => console.log(sheet.getName()));
// Below is the intended, currently non-functional, behaviour
const newCurrentSheet = spreadsheet.getSheets().find((sheet) => {
return (url && url.indexOf(formId) > -1);
});
newCurrentSheet.setName(CURRENT_SHEET_NAME);
我尝试过以下方法，但都不起作用：
- 在窗体上使用
getDestinationId，并在运行getSheets之前用SpreadsheetApp.openById重新绘制电子表格 - 在运行
.setDestination之后使用Utilities.sleep5秒(任意数(，以给API一些执行/思考时间
这感觉像个bug，但在重新链接表单后，也许还有另一种方法可以获得电子表格的"刷新"视图？或者我错过了一些非常明显(可能(的东西。
很可能您的脚本没有得到新的表单响应表，因为添加该表后它缺少SpreadsheetApp.flush()。