Google Spreadsheet form script for employee work hours

Question

The form updates a spreadsheet with agents (employees) hours worked for a specific date. The form has 4 pages, a first page where the date and office is picked. After which the form continues to one of 3 pages which has the agent names for their particular office, and a field to enter hours.

The form submission columns are structured as such:

Timestamp Username Date Office agent1 agent2 agent3 [...] agent30

On form submission, the script runs and does the following:

It takes the information from the submission and adds them to a spreadsheet (currently received by opening the form and grabbing the last submission (rather than taking the event, I found that the event includes all agents across all offices with the rest just being blank, while the last submission only contains the fields that have values)) that has one date per row, all agents in columns and a column for summarizing the total hours for the office, as such:

Date office1 agent1 agent2 [...] office2 agent14 agent15 etc.

If the date doesn't exist in the sheet, it adds a new row, adds the date and copies the formulas, and fills in the agent hours. Any blank field is given a 0. Finally if the date is a date before the previous rows date, the sheet is sorted.

If the date already exists in the sheet (as each submission only covers one office, this should be 3 submissions per date, disregarding any updates needed) all non-blank fields are updated. Blank fields are ignored.

function populateSheet() {
 // Matches agents to array and returns position 
 function findMatch(agent, arr) {
 for (var j = 0; j < arr.length; j++) {
 if (agent == arr[j].toLowerCase()) {
 return j + 1;
 }
 }
 return false;
 }
 // Matches dates to array and returns position
 function matchDate(date, dateArr) {
 for (var arr = 0; arr < dateArr.length; arr++) {
 if (+date == +dateArr[arr][0]) {
 return arr + 1;
 }
 }
 return false;
 }
 // Checks if there should be a formula, else sets to 0
 function matchFormulas(formulaRow, toRow) {
 for (var i = 0; i < formulaRow.length; i++) {
 if (isNaN(formulaRow[i]) && formulaRow[i].length > 0) {
 toRow.getCell(1, i + 1).setFormulaR1C1(formulaRow[i]);
 } else {
 toRow.getCell(1, i + 1).setValue(0);
 }
 }
 }
 // Populates the row if there is a number
 function populateRow(row, values, toRow) {
 for (var i = 0; i < row.length; i++) {
 // If the response is longer than 0, ie not blank
 if (row[i].getResponse().length > 0) {
 var isMatch = findMatch(row[i].getItem().getTitle().toLowerCase(), values);
 if (isMatch) {
 toRow.getCell(1, isMatch + 1).setValue(row[i].getResponse());
 }
 }
 }
 }
 // Open the spreadsheet, by ID, so let's hope it never ever ever changes
 var ss = SpreadsheetApp.openById('12345678abcdefghijklmnop');
 // Get the sheet
 var gdSheet = ss.getSheets()[0];
 // Get the last column as a number, because we'll use it, often
 var gdLastColumn = gdSheet.getLastColumn();
 // Get the agent names from the sheet
 var gdAgents = gdSheet.getRange(1, 2, 1, gdLastColumn).getValues()[0];
 // Grab the dates from the first column
 var gdDates = gdSheet.getRange(1, 1, gdSheet.getLastRow(), 1).getValues();
 // And open the form, by ID, so let's hope it never ever ever changes
 var form = FormApp.openById('12345678abcdefghijklmnop');
 // Get the responses from the form
 var formResponses = form.getResponses();
 // Get the last repsonse
 var formResponse = formResponses[formResponses.length - 1];
 // And itemize the response
 var itemResponses = formResponse.getItemResponses();
 // Get the selected date and parse it into something we can work with
 var formDate = itemResponses[0].getResponse();
 var dateSplit = formDate.split('-');
 var newDate = new Date(dateSplit[0], dateSplit[1] - 1, dateSplit[2], 00, 00, 00, 00);
 // Check if the date exists
 var dateMatched = matchDate(newDate, gdDates);
 // If it does, update the row
 if (dateMatched) {
 var gdRow = gdSheet.getRange(dateMatched, 1, 1, gdLastColumn);
 populateRow(itemResponses, gdAgents, gdRow);
 } else {
 // If it doesn't exist, add a new row at the bottom
 var gdLastRow = gdSheet.getRange(gdSheet.getLastRow(), 1, 1, gdLastColumn);
 gdSheet.insertRowAfter(gdSheet.getLastRow());
 var newLastRow = gdSheet.getRange(gdSheet.getLastRow() + 1, 1, 1, gdLastColumn);
 // Check for formulas and copy them accordingly
 var formulas = gdLastRow.getFormulasR1C1()[0];
 matchFormulas(formulas, newLastRow);
 // Add the hours entered to the new row
 populateRow(itemResponses, gdAgents, newLastRow);
 // Set the date in the first cell
 newLastRow.getCell(1, 1).setValue(newDate);
 // If this date is never than the previous date, sort the sheet.
 if (+newDate < +gdLastRow.getCell(1, 1).getValue()) {
 gdSheet.sort(1);
 }
 }
}

As it's a fairly basic thing, I feel it's a lot of code and time (2.5 seconds with 3 date rows). Is there a way to reduce the bloat and runtime?

Answer 1

The only thing I can suggest is to turn your array-lookups in to object-property lookups.

 function createAgentLookupTable( agents ) {
 var table = {};
 for (var i = 0 , length = agents.length; i < agents.length; i++) {
 agents[i].index = i;
 table[agents[i].toLowerCase()] = agents[i]
 }
 return table;
 }
 var AgentLookupTable = createAgentLookupTable( listOfAgents );

You can then simply look up an agent with

 function findMatch(agent) {
 var agent = AgentLookupTable[agent];
 return agent ? agent.index : false;
 } 

This ought to increase your lookup speeds by a ton, you can use the same approach for matchDate and matchFormulas.

• 200_success: I'm having trouble following your suggestion. What is arr[j]?
• konijn: Gah, I was too quick. j -> i, arr -> agents