0

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 1

0

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 sample


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.