Adding Values to a certain cell through code continuing until a certain cell using a for loop-CodePudding

Good Day! I know that the question is pretty vague and hard to understand so Im going to explain it as much as possible. I have a sheet that is ready to be printed and is a template of a document I am making. The cell numbers starts on row 19 and ends at row 49 and has text after that which is Certification.

RC Disb (OpEx), RC Disb (MBAP) is the same.

DV Logbook

CashDR

I usually use .getLastRow() when it starts at the start of the sheet but unfortunately right now there will be text written below the template and it starts at the middle of the sheet which makes is unusable so I opted to use a For Loop but I am unsure of how to use it in the current situation.

function sortSCA(){

const ws_lb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DV Logbook");
const ws_opex = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RCDisb (OpEx)");
const ws_mbap = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RCDisb (MBAP)");
const ws_cashdr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CashDR");

const columnB = ["B"]
const columnD = ["D"]
const columnF = ["F"]
const columnI = ["I"]

const timestamp_Range = ws_lb.getRange(columnB   lastrow_lb);
const payee_Range = ws_lb.getRange(columnD   lastrow_lb);
const particulars_Range = ws_lb.getRange(columnF   lastrow_lb);
const netAmount_Range = ws_lb.getRange(columnI   lastrow_lb);

const timestamp_value = timestamp_Range.getValue();
const payee_value = payee_Range.getValue();
const particulars_value = particulars_Range.getDisplayValue();
const netAmount_value = netAmount_Range.getDisplayValue();

const lastrow_lb = ws_lb.getLastRow();
const lastrow_opex = ws_opex.getLastRow();
const lastrow_mbap = ws_mbap.getLastRow();
const lastrow_cashdr = ws_cashdr.getLastRow();


  var range = ws_lb.getRange(1, 11, lastrow_lb, 1);
  var values = range.getValues();
  Logger.log(values);

  values.forEach(x => {
    if(x == "Operating Expenses"){
    for(i=19,49;i<1;i  ){
      ws_opex.getRange(i, 2, 1, 1).setValue(timestamp_value);
      ws_opex.getRange(i, 6, 1, 1).setValue(payee_value);
      ws_opex.getRange(i, 8, 1, 1).setValue(particulars_value);
      ws_opex.getRange(i, 9, 1, 1).setValue(netAmount_value);
      //cashdr
      ws_cashdr.getRange(lastrow_cashdr   1, 1, 1, 1).setValue(timestamp_value);
      ws_cashdr.getRange(lastrow_cashdr   1, 2, 1, 1).setValue(dmsNumber_value);
      ws_cashdr.getRange(lastrow_cashdr   1, 3, 1, 1).setValue(payee_value);
      ws_cashdr.getRange(lastrow_cashdr   1, 6, 1, 1).setValue(particulars_value);
      var grossAmountCashDR = ws_cashdr.getRange(lastrow_cashdr   1, 9, 1, 1)
      var grossAmountUse = grossAmountCashDR.getValue();
      grossAmountCashDR.setValue(grossAmount_value);
      var balanceCashDR = ws_cashdr.getRange(10, 10, 1, 1).getValue();
      ws_cashdr.getRange(lastrow_cashdr   1, 10, 1, 1).setValue(balanceCashDR - grossAmountUse);
    }

    } else if(x == "Medical Expense"){
    //opex
      var dateOpex = ws_mbap.getRange(13   lastrow_opex, 2, 1, 1).setValue(timestamp_value);
      var payeeOpex = ws_mbap.getRange(13   lastrow_opex, 6, 1, 1).setValue(payee_value);
      var particularsOpex = ws_mbap.getRange(13   lastrow_opex, 8, 1, 1).setValue(particulars_value);
      var amountOpex = ws_mbap.getRange(13   lastrow_opex, 9, 1, 1).setValue(netAmount_value);
      //cashdr
      var dateCashDR = ws_cashdr.getRange(15   lastrow_opex, 1, 1, 1).setValue(timestamp_value);
      var dvNumberCashDR = ws_cashdr.getRange(15   lastrow_opex, 2, 1, 1).setValue(dmsNumber_value);
      var payeeCashDR = ws_cashdr.getRange(15   lastrow_opex, 3, 1, 1).setValue(payee_value);
      var particularsCashDR = ws_cashdr.getRange(15   lastrow_opex, 6, 1, 1).setValue(particulars_value);
      var grossAmountCashDR = ws_cashdr.getRange(15   lastrow_opex, 9, 1, 1).setValue(grossAmount_value);
      var grossAmountUse = grossAmountCashDR.getValue();
      grossAmountCashDR.setValue(grossAmount_value);
      var balanceCashDR = ws_cashdr.getRange(10, 10, 1, 1).getValue();
      ws_cashdr.getRange(15   lastrow_opex, 10, 1, 1).setValue(balanceCashDR - grossAmountUse);
     }
  });
}

The forEach method is used because there are 2 template files which will be chosen depending on a certain value in the main sheet. I tried using a For Loop hoping that it helps with counting and recording the current cell the value will be placed in. Any help is welcome, Thanks!

And if you guys have any recommendations in making the code more efficient, I would happily take in any advice you have. God Bless!

Document Sample:

CodePudding user response：

Solved it! the key was using Offset. If anyone needs a follow up answer to this just comment. Thanks for the people that helped especially Logan for referring me a link.

CodePudding user response：

Posting discussions in chat as an answer:

I was just going through your script. I figured if your main issue is the getLastRow but now you have data below, how bout using this logic to get the last row? Find last row with Data in Column D starting the search at Row 4 in Google App Script

In your case, you'll set your range fixed to up to row 49, just before the certification.

Try:

const rowOffset = 12
const count = ws_opex.getRange("B13:B49").getDisplayValues().flat().filter(String).length;
const lastrow_opex = count   rowOffset;