Importing multiple user responses on a Form to a single row on a Sheet

Question

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.

Answer 1

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