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!
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 Sheet
s 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