I have the following script which matches the cell values in column P
, array x[10]
and column AA
, array x[21
], of the BLOCK ORDERS sheet with the prices from either the QUOTATIONS sheet or Products sheet and puts the price values in either columns AC
or AC
of the BLOCK ORDERS sheet. If the script runs perfect when there is a value in each row of the range of column P
and also in each row of column AA
. When there is a blank cell in either column P
or in column AA
it gives the error:
The number of rows in the data does not match the number of rows in the range. The data has 2 but the range has 3.
updateQuotationPrice @ Code.gs:56
I have tried changing the range but unable to get the solution:
ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, lr1 -1 , 1).setValues(output);
Here's the link for the sheet . Thank you for any assistance.
function updateQuotationPrice() {
var ss1 = SpreadsheetApp.openById("1UMO4Fxaz1hqjnUx9rPpVM0Z03B7uyfynvV5enJz8ZmU"); //BLOCK ORDERS Update
var ss2 = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk"); //Quotation
var ss3 = SpreadsheetApp.openById("1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL60"); //PRODUCTS
var svar = SpreadsheetApp.openById('1jbG_PLWU_eXfQTkwUn1krtYfzniw7HwnwnunhohzL60');
var shvar = svar.getSheetByName("VARIABLES");
// get part load variable and jobs
const ster13N = shvar.getRange(3,18,1,1).getValue();
const euro13N = shvar.getRange(3,19,1,1).getValue();
//Extract data from User Update
var lr1 = ss1.getSheetByName("BLOCK ORDERS").getLastRow();
var data1 = ss1.getSheetByName("BLOCK ORDERS").getRange(2, 6, lr1, 61).getValues();
//var price1 = ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, lr1, 1).getValues();
//var jobsRange = jobSheet.getRange("O2:BO" + jobSheet.getLastRow())
// var jobs = jobsRange.getValues()
//Extract data from Quotation Spreadsheet
var sheetNames2 = [];
ss2.getSheets().forEach(x => sheetNames2.push(x.getName()));
//Process data 1
var output = [];
data1.forEach((x,i) => {
var zoneSurcharge = x[58];
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10])).length > 0 ) { // added new condition here
if (x[9] == "EURO" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][3]+zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][2]+zoneSurcharge]);
}
}
else
if (x[9] == "EURO" && x[12] == "13 Newton" && x[10] != "" ) {surcharges = zoneSurcharge + euro13N
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][3]+surcharges]) ;
}
else
if (x[9] == "STERLING" && x[12] == "13 Newton" && x[10] != "") {surcharges = zoneSurcharge + ster13N
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][2]+surcharges]);
}
else
if (x[9] == "EURO" && x[12] == "10 Newton" && x[10] != "") {
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][3]+zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[12] == "10 Newton" && x[10] != "") {
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][2]+zoneSurcharge]);
}
});
console.log(output);
//Output
ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, lr1 -1 , 1).setValues(output);
//Process data 2
var output = [];
data1.forEach(x => {
var zoneSurcharge = x[58];
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[21])).length > 0) { // added new condition here
if (x[9] == "EURO" && x[21] !="") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[21]))[0][3]+zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[21] !="") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[21]))[0][2]+zoneSurcharge]);
}
}
else
if (x[9] == "EURO" && x[12] == "13 Newton" && x[21] !="") {surcharges = zoneSurcharge + euro13N
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[21]))[0][3]+surcharges]) ;
}
else
if (x[9] == "STERLING" && x[12] == "13 Newton" && x[21] !="") {surcharges = zoneSurcharge + ster13N
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[21]))[0][2]+surcharges]);
}
else
if (x[9] == "EURO" && x[12] == "10 Newton" && x[21] !="") {
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[21]))[0][3]+zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[12] == "10 Newton" && x[21] !="") {
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[21]))[0][2]+zoneSurcharge]);
}
});
//Output
ss1.getSheetByName("BLOCK ORDERS").getRange(2, 29, lr1 - 1, 1).setValues(output);
}