Google Spreadsheet form script for employee work hours

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 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 0) {
var isMatch = findMatch(row[i].getItem().getTitle().toLowerCase(), values);
if (isMatch) {
toRo

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.

StackExchange Code Review Q#40034, answer score: 2