0

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);

 }
SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
Les
  • 75
  • 7
  • 3
    Try it this way: `ss1.getSheetByName("BLOCK ORDERS").getRange(2, 29, output.length, output[0].length).setValues(output);` – Cooper Feb 15 '23 at 22:57
  • 1
    Does this answer your question? [Determining the last row in a single column](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) – Tedinoz Feb 16 '23 at 00:04
  • 1
    Would it be possible for you to provide a sample sheet of other spreadsheets so that we can easily replicate the error that you are experiencing? It poses a challenge to visualise the other sheets based only on the script you have provided. – Century Tuna Feb 16 '23 at 01:03
  • @Cooper, yes this fixes the range error I was having, thankyou. Can you please advise how the range can the changed so the output is in the same row as the corresponding cell in column P rather than putting the output starting at row 2? – Les Feb 16 '23 at 15:30
  • I don't understand your last question – Cooper Feb 16 '23 at 21:30
  • @Cooper,the getRange is getRange(2, 26, output.length, output[0].length) which puts the output value in row 2 and row 3 of column 26. The output values are set from column 16, indice x[10] which values are in row 3 and row 4. I require the output values in column 26 to be and row 3 and row 4 also. I hope you can follow this – Les Feb 16 '23 at 21:44
  • It starts write output at row 2 not row 3. I don't care where the output was gathered from . That's independant of the write. – Cooper Feb 16 '23 at 21:45
  • If you need to change the contents of the output go ahead and do it. You initial question was about the setValues() statement. – Cooper Feb 16 '23 at 21:47
  • @Cooper, Ok understood. Any suggestions as how to I might modify so the output depends on where it was gathered from? – Les Feb 16 '23 at 21:48
  • 1
    The output always depends upon where it was gathered. I don't understand your question. – Cooper Feb 16 '23 at 21:58

0 Answers0