Form responses spreadsheet.getSheets() doesn't return responses sheet?

Question

My overall goal is to create a new 'responses' sheet (in the same Spreadsheet) each week for a Form. At the end of processing the week's responses, given the limitations on Form Responses Spreadsheets (not being able to delete rows), I try to do the following:

  // ...

  // 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);

I've tried the following, none of which worked:

Using getDestinationId on the Form and refetching the spreadsheet with SpreadsheetApp.openById before running getSheets
Using Utilities.sleep for 5 seconds (arbitrary number) after running .setDestination to give the API some execution/thinking time.

This feels like a bug, but perhaps there's another way to get a 'refreshed' view of the Spreadsheet after re-linking the Form? Or I've missed something really obvious (likely).

I suggest changing your goal. Don’t change the response sheet just create a weekly report sheet using a time based trigger and a function that reads the current linked sheet — Cooper
The code isn't complete. How is the script assigning an objecto to currentSheet? How are you calling the script? — Rubén
@Cooper thank you, that's given me an idea – I suppose I could copy the week's answers into a new sheet as the very first step, then continue as planned from there without wiping the responses sheet. 🤔 — okeegan

Rubén Rubén · Accepted Answer · 2020-08-02T20:17:02

It's very likely that your script is not getting the new form responses sheet because it's missing SpreadsheetApp.flush() after that sheet was added.

Form responses spreadsheet.getSheets() doesn't return responses sheet?

1 Answers