CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
okeegan
okeegan

Reputation: 93

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

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:

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).

Upvotes: 0

Views: 201

Answers (1)

Wicket
Wicket

Reputation: 38140

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.

Upvotes: 2

Related Questions