Searching a Google Form response (Google Sheets) for row and column numbers

Question

I am trying to figure out how to search a google forms response (google sheets) in order to find a value of the row and column number to extract data.

I have everything else, but searching for the column and row and I'm not sure how to make a 2D search for this.

Here is the data I want to search and extract, but I don't want all of the data, just specific cells/ranges.

https://docs.google.com/spreadsheets/d/1ehA0fXBM6CPBAKfHvBbouRPjOMDxrXSdFI0zMye3G7c/edit?usp=sharing

My plan is to pull the "Employee Name" into another spreadsheet for an overview of who has completed it and at what location on a daily basis. Because the form is always receiving responses I want the script to pull data multiple times a day to show compliance of the form. I want the response to look something like this:

https://docs.google.com/spreadsheets/d/1QyK5c-vxkt5BnM-wM-m80RZCO_29KWoF_isIZBOllvQ/edit?usp=sharing

Here is what I was hoping to get my Google Script to do: 1. Script searches source sheet finds location. 2. Script returns row and column for where the location was listed. 3. Script takes row and column and subtracts 1 from the row and getValue(); of that cell. (Which in this case will be the employee who completed the form.) 4. Script returns the "employee name" to destination sheet based on employee location. (So it is in the right line.) Result, script pulls data from the form responses and lists who completed it.

I have everything working to pull data from a specific cell/range and paste it to the destination sheet based on location and the date.

Now that I've explained what I'm trying to do, I can explain where I'm stumped. I can't figure out how the repeating for or when command works. I want to search for one specific location and than return the row and column number. Here is the code I have been trying to manipulate, but I can't get it to search in 2d or even the row to return a value int eh for/when command.

function updateVerification() {
  var ss = SpreadsheetApp.openById("1ehA0fXBM6CPBAKfHvBbouRPjOMDxrXSdFI0zMye3G7c");
  var sheet = ss.getSheetByName("Sheet1");
  var ss_dest = SpreadsheetApp.openById("1QyK5c-vxkt5BnM-wM-m80RZCO_29KWoF_isIZBOllvQ");
  var sheet_dest = ss_dest.getSheetByName("Destination");
  var rows=ss.getDataRange().getValues();
  var paste = 0;
  for(var r=0; r<rows.length; r++){
    for(var c=0; c<rows[r].length; c++){
      if(rows[r][c] == "Location #1"){
        paste = sheet.getRange(rows[r]+":"+rows[c]).getValue();
        sheet_dest.getRange(2,4).setValue(paste);
      }
    }
  }
}

If anyone is able to help me I would greatly appreciate it.

Thanks for any and all help. :)

You need to call getDataRange() with parentheses and getValues(), plural. Its also not clear where targetRange is coming from. — dwmorrin

Cooper Cooper · Accepted Answer · 2019-04-23T01:43:12

I'm guessing you want something like this:

function updateVerification() {
  var ss=SpreadsheetApp.openById("1ehA0fXBM6CPBAKfHvBbouRPjOMDxrXSdFI0zMye3G7c");
  var sh=ss.getSheetByName("Sheet1");
  var ss_dest=SpreadsheetApp.openById("1QyK5c-vxkt5BnM-wM-m80RZCO_29KWoF_isIZBOllvQ")
  var sh_dest=ss_dest.getSheetByName("Destination");
  var rows=ss.getDataRange().getValues();
  for(var r=0;r<rows.length;r++) {
    for(var c=0;c<rows[r].length;c++) {
      if(rows[r][c]=="Location #1") {
        sh_dest.appendRow(rows[r]);
      }
    }
  }
}

If I remove the cell contents and replace it with the array indices your data would look something like this:

[
["row[0][0]","rows[0][1],"rows[0][2]"],
["row[1][0]","rows[1][1],"rows[1][2]"],
["row[2][0]","rows[2][1],"rows[2][2]"],
["row[3][0]","rows[3][1],"rows[3][2]"]//rows[rows.length-1]
]

Searching a Google Form response (Google Sheets) for row and column numbers

1 Answers