0

I am new to scripting and not sure how to approach that.

In short - I want the script to copy values from range L3:L118 to N3:N118 until a value of P3 (sum of differences between the 2 ranges) is 0.00. I managed to create a macro for the copying phase, however not sure how to loop it

Here is the code

function GetthepriceMK3() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.getRange('N3:N118').activate();
 spreadsheet.getRange('L3:L118').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Would anyone be able to help with the loop? No idea how to start and the ifs

Rubén
  • 34,714
  • 9
  • 70
  • 166
jacek rzepny
  • 1
  • What do you mean "sum of differences between 2 ranges"? Unless the cells in a row (say, L10 and N10) are equal? It would be easier if you just show the numbers. What you have and what you want to gain. – Yuri Khristich Feb 23 '22 at 01:14
  • 2
    Welcome to [so]. If you haven't done yet, please read https://developers.google.com/apps-script/guides/sheets. One of the things that you will learn there is that Google Apps Script uses JavaScript as programming language. This programming language has several ways to do loops. One nice resource about loops in JavaScript is https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Loops_and_iteration. If you need further help, please show what you tried and add brief description of your search efforts as is suggested in [ask]. – Rubén Feb 23 '22 at 02:15
  • Related https://stackoverflow.com/q/52080/1595451 – Rubén Feb 23 '22 at 02:19

1 Answers1

0

Collect sum of differences between L and N until sum equals v

function GetthepriceMK3() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const l = sh.getRange("L3:L118").getValues();
  const n = sh.getRange("N3:N118").getValues();
  let v = 0;
  let s = 0;
  for (var i = 0; i < l.length; i++) {
    s += l[i][0] - n[i][0];
    if (s == v) {
      break;//stops the loop
    }
  }
  Logger.log(i);//row is i + 3
  Logger.log(s);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54