I have sheet1 with 17 columns of data. Order number in in Column H, date & time is in Column L. Sheet2 has 12 columns of data imported with importrange. Order Number is in Col K, Date & Time in in Col I (shown in script as Col L but would like to move it to Col I)
When the sheet2 order number matches sheet1 order number, I’d like the dates from sheet2 to overwrite the dates in sheet1.
I’m trying to repurpose this script but keep getting error “The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1.” Do I have to rearrange my data columns or can this work with current columns?
function replaceDates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName('FFDataPT');
var sh1lastRow = sh1.getLastRow();
// Sheet1!H:H values for RefNum in FFDataPT
var sh1HValues = sh1.getRange(2, 8, sh1lastRow, 1).getValues().flat();
// Sheet1!A:J range of data in MLB Status to replace data in FFDataPT
var sh1AJRange = sh1.getRange(2, 12, sh1lastRow,1);
//Logger.log("DEBUG: sh1AJRange ="+sh1AJRange.getA1Notation());
//Logger.log("DEBUG: sh1HValues ="+sh1HValues);
var sh2 = ss.getSheetByName('MLB Status');
var sh2lastRow = sh2.getLastRow();
// Sheet2!K:K values for RefNum in MLB Status
var sh2KValues = sh2.getRange(2, 9, sh2lastRow, 1).getValues().flat();
// Sheet2!L:L values for Updated Date in MLB Status
var sh2LValues = sh2.getRange(2, 11 , sh2lastRow, 1 ).getValues().flat();
// Logger.log("DEBUG: sh2KValues ="+sh2KValues);
// Logger.log("DEBUG: sh2LValues ="+sh2LValues);
// for every values of Sheet1!H:H refNum in FFDataPT that matches RefNum in MLB Status
var output = sh1HValues.map(row => {
// get position of every value in Sheet1!H:H from Sheet2!K:K
// index is -1 if RefNum value is not found in Sht2 K:K
var index = sh2KValues.indexOf(row);
// if position is not -1 (means it is found)
if(index >= 0)
// return the corresponding value in Sheet2!L:L
return [sh2LValues[index]];
// return blank if value in Sheet1!L:L is not found in Sheet2!K:K
return [];
});
// set Sheet1!A:L range with value of generated array
sh1AJRange.setValues(output);
}
Range Mismatch
The main issue is that you are returning an empty array whenever the if statement falls into -1 which caused a mismatch within your range. In order to fix this, you need to retain the original values of your data in the range.
Some improvement:
Date & Time in Col I (shown in script as Col L but would like to move it to Col I) – I have adjusted my code with your column preference. Please be aware of this when running the script.
NOTE: If you have a column with a header, the best way to getRange() is to use a -1 in the number of rows. E.g var sh1LRange = sh1.getRange(2, 12, sh1lastRow - 1, 1); since you started with row 2 you must remove one in the sh1lastRow - 1 to avoid getting extra blank cell.
Complete code:
function replaceDates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName('FFDataPT');
var sh1lastRow = sh1.getLastRow();
var sh1HValues = sh1.getRange(2, 8, sh1lastRow - 1, 1).getValues().flat();
var sh1LRange = sh1.getRange(2, 12, sh1lastRow - 1, 1);
var sh1LValues = sh1LRange.getValues();
var sh2 = ss.getSheetByName('MLB Status');
var sh2lastRow = sh2.getLastRow();
var sh2KValues = sh2.getRange(2, 11, sh2lastRow - 1, 1).getValues().flat();
var sh2IValues = sh2.getRange(2, 9, sh2lastRow - 1, 1).getValues().flat();
var output = sh1HValues.map((row, index) => {
var matchIndex = sh2KValues.indexOf(row);
if (matchIndex >= 0) {
return [sh2IValues[matchIndex]];
} else {
return [sh1LValues[index][0]];
}
});
sh1LRange.setValues(output);
}
Sample result:
Reference:getRange(), Map
4