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

Question

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.

Can I ask you about the relationship between `Operating Expenses` and `values` and your showing sample image? And, it seems that `ws_lb`, `ws_opex` and `ws_cashdr` are not declared. And also, it seems that the values of `timestamp_value`, `payee_value`, and so on are not declared. But, you say `unfortunately right now there will be text written below the template`. So, I'm worried that you might have miscopied your current script. How about this? — Tanaike, Jan 19 '23 at 00:54
Good day sir Tanaike! range and values are the values that are taken from column K from the Form Response sheet which has two possible Values, Operating Expense and Medical Expense, The forEach Method is used to determine what value it is from both which will be used to sort it out in their respective report template which is seen above. ws_lb is the mainsheet while ws_opex and ws_mbap is the report template mentioned. ws_cashdr is the sheet that holds the final values of all. I will update the script for you to properly see as these values are named constant at the start of the code. — Dean, Jan 19 '23 at 01:15
I have now updated the script. Anything else you need? and if you have an idea as to how I can improve the space complexity/efficiency of my code would be much appreciated as I am open to learn any method I can pick up. Thanks! @Tanaike — Dean, Jan 19 '23 at 01:20
Thank you for replying. From your updated script, in this case, I thought that when a sample Spreadsheet can be used, it will help to test your script. By the way, you have already confirmed that your showing script works fine. Is my understanding correct? — Tanaike, Jan 19 '23 at 01:26
Yes, everything else works fine aside from the fact that I cant get it to just put the values from cell 19 onwards to cell 49 each form submit which is the main point of my question. — Dean, Jan 19 '23 at 01:35
Thank you for replying. From `Yes, everything else works fine aside from the fact that I cant get it to just put the values from cell 19 onwards to cell 49 each form submit which is the main point of my question.`, I understood about your current script. — Tanaike, Jan 19 '23 at 01:36
Please share a copy/sample of your spreadsheet, remove sensitive data if any — Logan, Jan 19 '23 at 02:38
If possible you can make a copy of the sheet itself and share link instead of screenshots so that we can easily replicate what you have and not manually set it up on our own. — Logan, Jan 19 '23 at 06:02
@Logan Done! Inputs come in from Google Forms and into DV Logbook Sheet. FYI — Dean, Jan 19 '23 at 06:14
Thank you for replying and providing your sample Spreadsheet. Now, I noticed that the discussion has advanced. In this case, I would like to respect the existing discussion. — Tanaike, Jan 19 '23 at 06:20
No problem. I'm sorry it took long I had to run some errands. — Dean, Jan 19 '23 at 06:27
Let us continue discussion in chat @Dean https://chat.stackoverflow.com/rooms/251256/75166762 — Logan, Jan 19 '23 at 06:35
Thank you for replying. It's no problem. I believe your issue will be resolved. — Tanaike, Jan 19 '23 at 06:39
hasnt been solved yet. sample spreadsheet and code is in the link above. — Dean, Jan 20 '23 at 00:30

score 0 · Accepted Answer · answered Jan 21 '23 at 05:00

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;

yup! its exactly what i used when you referred the link to me. thanks man! — Dean, Jan 26 '23 at 05:55

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

1 Answers1