Good Day! I would like the Purchase Order ID repeated automatically upon submission of a form in an earlier sheet. My current code however repeats this throughout the length of data range, rather than for only cells with contents in the said data range. Can I get help in modifying the code or loop/if statement below? Thank you in advance.

function exportPO() {
var myGooglSheet = SpreadsheetApp.getActive();
var shSource = myGooglSheet.getSheetByName("Source");
var shDatabase = myGooglSheet.getSheetByName("Database");
var lastRow = shDatabase.getLastRow()

var ProductValues = shSource.getRange("B5:C14").getValues();
shDatabase.getRange(lastRow 1,2,ProductValues.length,2).setValues(ProductValues);

var PO = shSource.getRange("C1").getValues();
var lines = 0;

var ProductRow = ProductValues.length;
for(var i=0; i<ProductRow; i  )
  if (ProductValues!=''){
  lines= lines 1;
  shDatabase.getRange(lastRow lines,1).setValues(PO);
  shDatabase.getRange(lastRow lines,4).setValue(new Date()).setNumberFormat('yyyy-mm- 
  dd h:mm'); //Submitted On

CodePudding user response：

In relation to

var ProductValues = shSource.getRange("B5:C14").getValues();

The above line assigns a Array of Arrays to ProductValues

In relation to

ProductValues!=''

The above expression always will return false. You might change this to ProductValues[i].every(value => value != '') to compare all the values in a row to an empty string, if all are not a empty string, this will return true, otherwise false, or change the original expression to something like ProductValues[i][0]!='' to compare on value of each row to an empty string, the first index corresponds to the row, the second to the column.