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.