I am attempting to run a script on Google Sheets that creates a new tab with all new form responses or puts the data of a new form response into it's tab if it's already created. I'm following instructions from Hyde here: https://support.google.com/docs/thread/75611642?msgid=75815726&sjid=8281746901339270693-NA
When I install it and run it in AppsScript, I receive this error:
Error
TypeError: Cannot read properties of undefined (reading 'name')
showMessage_ @ Code.gs:338
showAndThrow_ @ Code.gs:325
firstTimeCopy @ Code.gs:110
I've tried to delete, manipulate, and fix this error and reference other posts, but nothing is working on my end!
Full Script:
/**
* Automatically copies form responses as they are submitted to several
* category sheets by date, as indicated by the timestamp in column A.
* New category sheets are inserted when missing using a template sheet.
*
* To take this script into use:
*
* - take a backup of your spreadsheet through File > Make a copy
* - select all the text in this script, starting at the first "/**"
* line above, and ending at the last "}"
* - copy the script to the clipboard with Control+C (on a Mac, ⌘C)
* - open the spreadsheet where you want to use the script
* - choose Tools > Script editor > Blank (this opens a new tab)
* - if you see just the 'function myFunction() {}' placeholder, press
* Control+A (on a Mac, ⌘A), followed by Control+V (⌘V) to paste
* the script in
* - otherwise, choose File > New > Script file, then press
* Control+A (⌘A) followed by Control+V (⌘V) to paste the script in
* - modify the settings under "START modifiable parameters" as necessary
* - press Control+S (⌘S) to save the script
* - when prompted, name the project 'Copy rows to tabs'
* - if you have existing data in the master sheet, and want to copy
* that existing data to category sheets in addition to copying new rows
* as they are submitted, choose Run > Run function > firstTimeCopy
* - when prompted, click Review Permissions > choose account > Advanced >
* Go to Copy rows to tabs > Allow (you only need to do this once)
* - go back to the spreadsheet tab and click Yes in the dialog box
* - to make the script run automatically as new data is submitted,
* go to the script editor tab and choose
* Run > Run function > installOnFormSubmitTrigger
* - the script will then run automatically as new data is submitted
*
* @OnlyCurrentDoc
* @license https://hyde.mit-license.org/2019
* @see https://support.google.com/docs/thread/75611642?msgid=75745289
*/
/**
* Globals.
*/
let settings = {};
function initializeSettings_() {
try {
const ss = SpreadsheetApp.getActive();
////////////////////////////////
// [START modifiable parameters]
const masterSheet = ss.getSheetByName('Listing_Feedback');
settings = {
app: {
name: 'Copy rows to tabs',
},
master: {
sheet: masterSheet,
name: masterSheet.getName(),
columnLabelRow: masterSheet.getFrozenRows() || 1,
categoryColumn: 1, // column A = 1, B = 2, C = 3, etc.
},
template: {
sheet: ss.getSheetByName('Template for daily sheets'),
},
getTabName_: function (value) {
switch (typeof value) {
case 'string':
return value ? value : null;
case 'number':
return value === value ? String(value) : null; // handle NaN
case 'boolean':
return String(value);
}
try {
return Utilities.formatDate(value, ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');
} catch (error) {
return null;
}
},
spreadsheet: ss,
};
// [END modifiable parameters]
////////////////////////////////
} catch (error) {
showAndThrow_(error);
}
}
/**
* Run this function manually, ONCE, to copy all existing data from
* the master sheet to category sheets.
*/
function firstTimeCopy() {
// version 1.0, written by --Hyde, 9 October 2020
// - initial version
try {
const master = settings.master;
if (!confirm_(
'This function will copy all existing data to tabs by category.\n\n' +
'You should only run this function ONCE when setting up the tabs.\n\n' +
"Are you sure you want to create new tabs and copy data from '" +
master.name + "' to tabs named by the categories in column '" +
master.sheet.getRange(master.columnLabelRow, master.categoryColumn).getDisplayValue() + "'?")) {
return;
}
const rows = getRangeIntersection_(master.sheet.getDataRange(), master.sheet.getRange((master.columnLabelRow + 1) + ':' + master.sheet.getLastRow()));
if (!rows) {
showMessage_('Done. Could not find any rows to process.', 30);
}
copyRowsToTabsByCategory_({ range: rows.range });
} catch (error) {
showAndThrow_(error);
}
}
/**
* Copies data rows from a master sheet to several category sheets
* by the categories listed in a master sheet category column.
* New category sheets are inserted when missing using a template sheet.
*
* @param {Object} e The event object.
*/
function copyRowsToTabsByCategory_(e) {
// version 1.0, written by --Hyde, 9 October 2020
// - initial version
// - @license https://hyde.mit-license.org/2020
try {
const event = getEventObject_(e);
showMessage_('Copying rows...');
const rows = event.range.getValues();
let numRows = 0;
const dataByTab = rows.reduce(function (data, row) {
const tabName = settings.getTabName_(row[settings.master.categoryColumn - 1]);
if (!tabName) {
return data;
}
if (!data.hasOwnProperty(tabName)) {
data[tabName] = [];
}
data[tabName].push(row);
numRows += 1;
return data;
}, {});
const ss = settings.spreadsheet;
for (let tabName in dataByTab) {
let sheet = ss.getSheetByName(tabName);
if (!sheet) {
sheet = ss.insertSheet(tabName, ss.getNumSheets(), { template: settings.template.sheet });
}
const dataToAppend = dataByTab[tabName];
sheet.getRange(sheet.getLastRow() + 1, 1, dataToAppend.length, dataToAppend[0].length).setValues(dataToAppend);
}
showMessage_('Done. Copied ' + numRows + ' rows to ' + Object.keys(dataByTab).length + "' tabs: '" + Object.keys(dataByTab).join("', '") + "'.");
} catch (error) {
showAndThrow_(error);
}
}
/**
* Determines the type of a spreadsheet event and populates an event object.
*
* @param {Object} e The original event object.
* @return {Object} An event object with the following fields, or null if the event type is unknown.
* {Spreadsheet} spreadsheet The spreadsheet that was edited.
* {Sheet} sheet The sheet that was edited in spreadsheet.
* {Range} range The cell or range that was edited in sheet.
* {String} sheetName The name of the sheet that was edited.
* {Number} rowStart The ordinal number of the first row in range.
* {Number} rowEnd The ordinal number of the last row in range.
* {Number} columnStart The ordinal number of the first column in range.
* {Number} columnEnd The ordinal number of the last column in range.
* {Number} numRows The number of rows in range.
* {Number} numColumns The number of columns in range.
* {String} eventType One of ON_EDIT, ON_CHANGE or ON_FORM_SUBMIT.
* {String} changeType Always EDIT, and never INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER.
* {String} authMode One of ScriptApp.AuthMode.NONE, .LIMITED, .FULL or .CUSTOM_FUNCTION.
*/
function getEventObject_(e) {
// version 1.4, written by --Hyde, 9 October 2020
// - remove |JSON.stringify(e.range) !== '{}'|
// version 1.3, written by --Hyde, 9 July 2020
// - add Javadoc for eventType
// version 1.2, written by --Hyde, 9 July 2020
// - remove moveRowsFromSpreadsheetToSpreadsheet_ optimizations
// - replace |e.range.getLastRow()| with |event.range.getLastRow()|, ditto for getLastColumn
// version 1.1, written by --Hyde, 29 June 2020
// - use Number()
// version 1.0, written by --Hyde, 27 June 2020
// - initial version
try {
if (!e) {
return null;
}
var event = {};
if (e.range) { // triggered by ScriptApp.EventType.ON_EDIT or .ON_FORM_SUBMIT
event.range = e.range;
event.rowStart = Number(e.range.rowStart);
event.rowEnd = Number(e.range.rowEnd);
event.columnStart = Number(e.range.columnStart);
event.columnEnd = Number(e.range.columnEnd);
event.changeType = 'EDIT';
event.eventType = e.namedValues ? 'ON_FORM_SUBMIT' : 'ON_EDIT';
} else if (e.changeType === 'EDIT') { // triggered by ScriptApp.EventType.ON_CHANGE
// @see https://developers.google.com/apps-script/guides/triggers/events#change
// @see https://community.glideapps.com/t/new-row-in-spreadsheet-for-every-user/6475/55
var ss = SpreadsheetApp.getActive();
event.range = ss.getActiveRange();
event.rowStart = event.range.getRow();
event.rowEnd = event.range.getLastRow();
event.columnStart = event.range.getColumn();
event.columnEnd = event.range.getLastRow();
event.changeType = e.changeType;
event.eventType = 'ON_CHANGE';
} else { // triggered by some other change type
return null;
}
event.authMode = e.authMode; // @see https://developers.google.com/apps-script/reference/script/auth-mode
event.sheet = event.range.getSheet();
event.sheetName = event.sheet.getName();
event.spreadsheet = event.sheet.getParent();
event.numRows = event.rowEnd - event.rowStart + 1;
event.numColumns = event.columnEnd - event.columnStart + 1;
return event;
} catch (error) {
showAndThrow_(error);
}
}
/**
* Returns the intersection of two ranges as an object that contains the sheet, a new range, and the range's grid coordinates and dimensions.
*
* @param {Range} range A spreadsheet range object.
* @param {Range} intersectingRange A spreadsheet range object that possibly overlaps range.
* @return {Object} The intersection of range and intersectingRange, or null if they do not overlap. The return object has these fields:
* sheet A sheet where range is.
* range A range that represents the intersection of range and intersectingRange.
* rowStart The first row of the intersection.
* columnStart The first column of the intersection.
* rowEnd The last row of the intersection.
* columnEnd The last column of the intersection.
* numRows The number of rows in the intersection.
* numColumns The number of columns in the intersection.
*/
function getRangeIntersection_(range, intersectingRange) {
// version 1.3, written by --Hyde, 6 October 2020
// - rename fields in the return object
// version 1.2, written by --Hyde, 1 July 2020
// - add |var sheet = range.getSheet()|
// version 1.1, written by --Hyde, 18 June 2020
// - add sheet in return object
// version 1.0, written by --Hyde, 22 January 2019
// - initial version
var sheet = range.getSheet();
if (sheet.getSheetId() !== intersectingRange.getSheet().getSheetId()) {
return null;
}
var rowStart = Math.max(range.getRow(), intersectingRange.getRow());
var rowEnd = Math.min(range.getLastRow(), intersectingRange.getLastRow());
if (rowStart > rowEnd) {
return null;
}
var columnStart = Math.max(range.getColumn(), intersectingRange.getColumn());
var columnEnd = Math.min(range.getLastColumn(), intersectingRange.getLastColumn());
if (columnStart > columnEnd) {
return null;
}
return {
sheet: sheet,
range: sheet.getRange(rowStart, columnStart, rowEnd - rowStart + 1, columnEnd - columnStart + 1),
rowStart: rowStart,
columnStart: columnStart,
rowEnd: rowEnd,
columnEnd: columnEnd,
numRows: rowEnd - rowStart + 1,
numColumns: columnEnd - columnStart + 1,
};
}
/**
* Installs a trigger that runs each time a form is submitted.
* Deletes any previous instances of ON_FORM_SUBMIT triggers.
*
* To permanently install the trigger, choose Run > Run function > installOnFormSubmitTrigger.
* You only need to install the trigger once per spreadsheet.
* To review the installed triggers, choose Edit > Current project's triggers.
*/
function installOnFormSubmitTrigger() {
// version 1.0, written by --Hyde, 7 May 2020
// - initial version
deleteTriggers_(ScriptApp.EventType.ON_FORM_SUBMIT);
ScriptApp.newTrigger('copyRowsToTabsByCategory_')
.forSpreadsheet(SpreadsheetApp.getActive())
.onFormSubmit()
.create();
}
/**
* Deletes all installable triggers of the type triggerType associated with the current project and current user.
*
* @param {EventType} triggerType One of ScriptApp.EventType.ON_EDIT, .ON_FORM_SUBMIT, .ON_OPEN, .ON_CHANGE, .CLOCK (time-driven triggers) or .ON_EVENT_UPDATED (Calendar events).
*/
function deleteTriggers_(triggerType) {
// version 1.0, written by --Hyde, 7 May 2020
// - initial version
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0, numTriggers = triggers.length; i < numTriggers; i++) {
if (triggers[i].getEventType() === triggerType) {
ScriptApp.deleteTrigger(triggers[i]);
}
}
}
/**
* Shows error.message in a pop-up and throws the error.
*
* @param {Error} error The error to show and throw.
*/
function showAndThrow_(error) {
// version 1.0, written by --Hyde, 16 April 2020
// - initial version
var stackCodeLines = String(error.stack).match(/\d+:/);
if (stackCodeLines) {
var codeLine = stackCodeLines.join(', ').slice(0, -1);
} else {
codeLine = error.stack;
}
showMessage_(error.message + ' Code line: ' + codeLine, 30);
throw error;
}
/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
* @param {Number} timeoutSeconds Optional. The number of seconds before the message goes away. Defaults to 5.
*/
function showMessage_(message, timeoutSeconds) {
// version 1.0, written by --Hyde, 16 April 2020
// - initial version
SpreadsheetApp.getActive().toast(message, settings.app.name, timeoutSeconds || 5);
}
/**
* Shows a dialog box with buttons for Yes and No.
*
* @param {String} message The message to show.
* @return {Boolean} True if the user clicked Yes, otherwise false.
*/
function confirm_(message) {
// version 1.0, written by --Hyde, 9 October 2020
// - initial version
const ui = SpreadsheetApp.getUi();
return ui.Button.YES === ui.alert(settings.app.name, message, ui.ButtonSet.YES_NO);
}
