Find any Form Response sheet in a workbook

Question

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!

tehhowch · Accepted Answer · 2018-07-23T16:58:29.900

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:

edited Jul 23 '18 at 16:58

answered Jul 23 '18 at 16:48

tehhowch

9,645
4
24
42

@I'-'I indeed. If OP has them, then they are either a static view (no longer a true "response" sheet) or updated by other code, in which case that other code knows how to find said sheets and the logic from said code can be used in lieu of `getFormUrl` :) – tehhowch Jul 23 '18 at 17:21
Thanks for the response. I'm not sure how to parse it for my use though...chalk it up to my lack of experience :/ In this case, there's no question about whether or not there's a Form attached to the sheet. In fact, this rename function is tagged at the end of a separate one that creates a new form and links it to the sheet...so all of that is guaranteed. I also already have the ID of the spreadsheet. So basically I just need to grab that log of all form sheets in the spreadsheet (and then "do more stuff", as you said) – Alex Jul 23 '18 at 18:35
@Alex review [my answer here](https://stackoverflow.com/a/51484165/9337071) to see how you could get a particular form's response sheet, if you needed to work on only one rather than all of them. – tehhowch Jul 23 '18 at 19:37
Thanks for the help. I ended up figuring out an Occam's Razor situation...it appears that new form response tabs always get put into the beginning of the spreadsheet. Thus, I'm able to just use the index to do this! [[[ var formTab = ss.getSheets()[0]; formTab.setName('Entries'); ]]] Thanks though, I may come back to reference this again in the future! – Alex Jul 23 '18 at 20:55

Find any Form Response sheet in a workbook

1 Answers1

Linked