To find your matching value in the form response sheet, you must loop through the range to find a match. There are a number of ways to do that, I'll show a couple.
Here's a version of your saveChanges()
function that will get all the data from your destination sheet, look through it's column A for a match to the value in A40
, then update the data in that row.
function saveChanges() {
var uniqueIdColIndex = 0; // Col "A" has unique ID, is element 0 in row array
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheets()[0];
var destination = ss.getSheets()[1];
var sourceData = source.getRange("A40:BL40").getValues();
var destData = destination.getDataRange().getValues();
// Find coordinates of the row where value of cell A40 matches a cell in A:A in second spreadsheet
for (var rowIndex=0; rowIndex < destData.length; rowIndex++) {
if (sourceData[0][uniqueIdColIndex] == destData[rowIndex][uniqueIdColIndex]) {
// Found our match
destination.getRange(rowIndex+1,1,sourceData.length,sourceData[0].length)
.setValues(sourceData);
break; // Done, exit loop
}
}
}
Here's another way to do it. This time, we don't read all the data in the destination sheet, only the info in column A. To be able to take advantage of array lookup methods, the two-dimensional array retrieved via .getValues()
needs to be transposed first - so we use a helper function to do that. (I'm using the transpose()
function from this answer.)
function saveChanges() {
var uniqueIdColIndex = 0; // Col "A" has unique ID, is element 0 in row array
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheets()[0];
var destination = ss.getSheets()[1];
var sourceData = source.getRange("A40:BL40").getValues();
// Get column A from destination sheet
var destDataTrans = transpose(destination.getRange(1, 1, destination.getLastRow(),1).getValues());
// Find coordinates of the row where value of cell A40 matches a cell in A:A in second spreadsheet
var destRow = destDataTrans[0].indexOf(sourceData[0]) + 1; // +1 to adjust to spreadsheet rows
if (destRow > 0) {
// Found our match
destination.getRange(destRow,1,sourceData.length,sourceData[0].length)
.setValues(sourceData);
}
}
The second approach has fewer lines of code, but should be a bit slower than the first one because of the transpose()
function which touches every element in column A before performing a search with .indexOf()
. (The first approach searched in place, and exited once a match was found, so it actually does less work.)
In both examples, I've tried to limit the calls to google's services as much as possible. Alternatively, you could read info from the spreadsheets inside the search loop, which would be much slower, but would avoid the +1 / -1
mental gymnastics needed to keep 0-based arrays aligned with 1-based rows and columns.