I have a Google Form containing three linear scale questions. The form collects emails and then a value between 1 and 3 for each question. I need the people who respond to the form to do so biweekly. I would like to see their answers added as new columns in the same row as their first round of answers in the corresponding spreadsheet. I do not want to have a new row created for them each time they respond to the Form. I tried researching IMPORTRANGE but that didn't work quite right. I assume I need to tell the sheet to look for matching emails but I'm not sure how to then add new columns with that email's responses to the existing sheet.
1 Answer
You want to accumulate form responses for three questions based on the email address entered in the form. There may be many ways to do this but I suggest a script.
Managing Data
The form responses will be displayed on a specific sheet.
In addition to this sheet, I suggest that you create a sheet devoted to the accumulated form responses for the three questions - you can format this appropriately.
In this answer, the sheet is called "data".
Sample layout for "data" sheet
Script processing
You need a script that will:
- use Event Objects so that you have easy access to the form submission details
- use an installable
On form submit
trigger that will execute the script whenever a new form response is received.
The script will:
- check the new email address against the existing email addresses on "data" using the Javascript
indexOf
method - if no match is found, then this is the first response by this user and you use
appendrow
to append the relevant form details to the bottom of the "data" sheet. - if a match is found, then you identify the matching row on the "data" sheet
- a subroutine calculates the next blank Column to the right of the row
- the question responses are pasted into adjacent blank columns using
setValues
Sample results
Code
function updatequestions(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// enter the name of the data sheet
var data_sheetname = "data"
var datasheet = ss.getSheetByName(data_sheetname)
// enter the number of header rows on the data sheet
var data_headers = 2
// get the form response data
//Logger.log(JSON.stringify(e))// DEBUG
// get the response values
var remail = e.values[1]
var q1 = e.values[2]
var q2 = e.values[3]
var q3 = e.values[4]
Logger.log("DEBUG: email address = "+remail+", Q1:"+q1+", Q2:"+q2+", Q3:"+q3)
// get the existing data
var dataValues = datasheet.getDataRange().getValues()
Logger.log("DEBUG: the length of data values = "+dataValues.length)
if (dataValues.length <= 2){ // no responses yet, this is the first
// append row
// create a temporary array and add the values to the array
var qresponses = []
qresponses.push(remail,q1,q2,q3)
Logger.log(qresponses)// DEBUG
// append the reply
datasheet.appendRow(qresponses)
Logger.log("DEBUG: first response - data appended")
}
else{
// there's already data in the sheet
// try and find the respondent email address in the existing data
// get the email address column
var dataEmail = dataValues.map(function(e){return e[0];})
Logger.log(dataEmail) // DEBUG
// find a match for the email address with a value in the email column
var remailidx = dataEmail.indexOf(remail)
Logger.log("DEBUG: email idx is "+remailidx)
if (remailidx == -1){ // if -1, then no match was found
// couldn't find this email in the existing replies
// append this reply.
// create a temporary array and add the values to the array
var qresponses = []
qresponses.push(remail,q1,q2,q3)
// append the reply
datasheet.appendRow(qresponses)
Logger.log("DEBUG: email not matched. Must be a first reply, so append data")
}else{
// found the email in the replies
var rowtocheck = remailidx+1
Logger.log("DEBUG: Found a match on row#"+rowtocheck)
// run subroutine to find next blank column in this row
var nextblankCol = getLastNonEmptyCellInRow(data_sheetname, rowtocheck)
Logger.log("DEBUG: the next blank column = "+nextblankCol)
// create a temporary array
var qresponses = []
// push response details but not the email address which is already in column A
qresponses.push([q1,q2,q3])
// update values on sheet
datasheet.getRange(rowtocheck,nextblankCol,1,3).setValues(qresponses)
}
}
SpreadsheetApp.flush()
}
/* adapted from StackOverflow
// Google Apps Script .getLastColumn(); of a specific row?
// Answer by Alan Wells
*/ https://stackoverflow.com/a/28783508/1330560
function getLastNonEmptyCellInRow(sheetname, row) {
var rowToCheck =row
var data_sheetname = sheetname
Logger.log("DEBUG: Subroutine: row = "+rowToCheck+", and sheet = "+data_sheetname)
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get the data sheet
var sheet = ss.getSheetByName(data_sheetname)
// get the last column with content in the sheet
var maxColumns = sheet.getLastColumn();
//var maxColumns = sheet.getLastRow();
Logger.log("DEBUG: Subroutine: max columns = "+maxColumns)
Logger.log("DEBUG: Subroutine: row data Range = "+sheet.getRange(rowToCheck, 1, 1, maxColumns).getA1Notation())
var rowData = sheet.getRange(rowToCheck, 1, 1, maxColumns).getValues();
Logger.log("row data#1")
Logger.log(rowData)
rowData = rowData[0]; //Get inner array of two dimensional array
Logger.log("row data#2")
Logger.log(rowData)
var rowLength = rowData.length;
Logger.log("DEBUG: Subroutine: the row length = "+rowLength)
for (var i = 0; i < rowLength; i++) {
var thisCellContents = rowData[i];
Logger.log('DEBUG: Subroutine: i='+i+', thisCellContents: ' + thisCellContents);
if (thisCellContents === "") {
Logger.log("DEBUG: Subroutine: The last column containing data = "+i);
var nextcolumn= i+1
Logger.log("DEBUG: Subroutine: the next column number = "+nextcolumn)
return nextcolumn; //Pass the count plus one, which is the last column with data
}
}
}
References
Event Objects
on form submit
indexOf
appendRow
setValues