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
// 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?

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.

• I'm having trouble following your suggestion. What is arr[j]? – 200_success Jan 27 '14 at 19:43
• Gah, I was too quick. j -> i, arr -> agents – konijn Jan 27 '14 at 19:49