Find any Form Response sheet in a workbook

2019-08-21 02:32发布

问题:

I'm having trouble inserting a regular expression into a super-simple Google script for a Sheet/Form. I've checked websites like regex101.com to be sure my syntax isn't a problem with the regex itself, I just can't figure out how to insert it into the Google script.

All I'm doing is trying to rename a "Form Responses" sheet generated by linking a form. I want it to catch any response sheet, so rather than "Form Responses 1," I want to give it a wildcard (if there's a better way to do this than with regex, I'm all ears). The script works just fine if I hard-code "Form Responses 1" as a string, so I don't need any troubleshooting for the rest of the code, just this line:

ss.getSheetByName('Form Responses 1').setName('Entries');

I've tried both /Form Responses .*/ and /^Form Responses/, as well as some of the suggestions I've seen using variables with new RegExp(), but every attempt results in an error that this field is null.

Any suggestions would be appreciated, thanks!

回答1:

You can only pass a string to getSheetByName. What you'll need to do is (perhaps after verifying that there even is a form submitting to the spreadsheet via Spreadsheet#getFormUrl()) is query all Sheets for a value of getFormUrl():

/**
 * @param {Spreadsheet} wb
 * @returns {Sheet[]} A possibly-empty array of sheets that have associated forms.
 */
function getAllFormResponseSheets(wb) {
  if (!wb) wb = SpreadsheetApp.getActive();
  return wb.getSheets().filter(function (sheet) { return sheet.getFormUrl(); });
}

Since a sheet with no form will return null, and null is falsy in JavaScript, only sheets which have a form submit to them will be included in the Array response.

You would then use this array to do whatever:

function foo() {
  const other = SpreadsheetApp.openById("....");
  const formSheets = getAllFormResponseSheets(other);
  formSheets.forEach(function (sheet) {
    Logger.log("My name is '%s' and I am index %s.", sheet.getName(), sheet.getIndex());
    // do more stuff with each sheet
  });
}

References:

  • Spreadsheet#getFormUrl
  • Spreadsheet#getSheetByName
  • Sheet#getFormUrl
  • Array#filter
  • falsy