CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Eric K.
Eric K.

Reputation: 131

How to Complete a For Loop to Set Values from list in One Sheet from Another?

The below function is meant to loop through the 'POTemplate' tab of my Google Sheet file and get several values starting in row 24 and list them out in a separate 'POHistory' tab within the same file. Currently, it is only grabbing those details from row 24 and is posting them 10 times to the 'POHistory' tab. Instead, I need it to getValues from only those cells that are populated in the POTemplate from row 24 to 34 and post to 'POHistory' once per entry. Hoping I am clear. 

function Submit() {
  var app = SpreadsheetApp;
  var orderSheet = 
app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var i = 24;
 for(i = 24; i<= 34; i++) {
  var poNO = orderSheet.getRange("h2").getValue();
  var poDate = orderSheet.getRange("h3").getValue();
  var vendor = orderSheet.getRange("c12").getValue();
  var skuNo = orderSheet.getRange(i, 3).getValue();
  var skuDesc = orderSheet.getRange(i, 4).getValue();
  var qty = orderSheet.getRange(i, 5).getValue();
  var uom = orderSheet.getRange(i, 6).getValue();
  var utCost = orderSheet.getRange(i, 7).getValue();
  var extCost = orderSheet.getRange(i, 8).getValue();
    var targetSheet = app.getActiveSpreadsheet().getSheetByName("POHistory");
     var nextRow = targetSheet.getLastRow() + 1; 
       targetSheet.getRange(nextRow, 1).setValue(poNO);
       targetSheet.getRange(nextRow, 2).setValue(poDate);
       targetSheet.getRange(nextRow, 3).setValue(skuNo);
       targetSheet.getRange(nextRow, 4).setValue(skuDesc);
       targetSheet.getRange(nextRow, 5).setValue(qty);
       targetSheet.getRange(nextRow, 6).setValue(uom);
       targetSheet.getRange(nextRow, 7).setValue(utCost);
       targetSheet.getRange(nextRow, 8).setValue(extCost);
 }}

Upvotes: 0

Views: 62

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

function Submit() {
  var ss=SpreadsheetApp.getActive();
  var osh=ss.getSheetByName('POTemplate');
  var tsh=ss.getSheetByName('POHistory');
  var rg=osh.getDataRange();
  var vA=rg.getValues();
  var poNO=vA[1][7];
  var poDate=vA[2][7];
  var vendor=vA[11][2];
  for(var i=24;i<=34;i++){
    var skuNo=vA[i-1][2];
    var skuDesc=vA[i-1][3];
    var qty=vA[i-1][4];
    var uom=vA[i-1][5];
    var utCost=vA[i-1][6];
    var extCost=vA[i-1][7];
    tsh.appendRow([poNO,poDate,skuNo,skuDesc,qty,uom,utCost,extCost]);
  }
}

Upvotes: 1

Related Questions