Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How to break an infinite loop bug when using for statement

I am currently working a small Google Apps Script that will get Data from a from and proceed to paste some of this data in a table where it finds matching ID / invoice number.

However, when these two for statement are following each other, the script takes ages to executed because it proceeds to add the data in the specified columns, great but also, from the first empty row up until the very end of the sheets it copies that same data indefinitely.

  for (var i = 0; i < caData.length; i++) {
    if (caData[i][3] == ID) {
      if (tax == "VAT") {
        feuilleCA.getRange(i + 1, 14).setValue(amount);
        feuilleCA.getRange(i + 1, 15).setValue(mode);
        feuilleCA.getRange(i + 1, 16).setValue(now);
      } else {
        feuilleCA.getRange(i + 1, 12).setValue(amount);
        feuilleCA.getRange(i + 1, 13).setValue(mode);
        feuilleCA.getRange(i + 1, 16).setValue(now);
      }
    }
  }

  for (var i = 0; i < caData.length; i++) {
    if (caData[i][1] == invoice) {
      if (tax == "VAT") {
        feuilleCA.getRange(i + 1, 14).setValue(amount);
        feuilleCA.getRange(i + 1, 15).setValue(mode);
        feuilleCA.getRange(i + 1, 16).setValue(now);
      } else {
        feuilleCA.getRange(i + 1, 12).setValue(amount);
        feuilleCA.getRange(i + 1, 13).setValue(mode);
        feuilleCA.getRange(i + 1, 16).setValue(now);
      }
    }
  }

To ensure that I did not make any mistake in the code previously provided, I proceeded to make tests with

The first for statement (ID) and it worked perfectly, no issue

The second for statement (invoice) and it worked perfectly, no issue.

However when combined, it seems to create some sort of infinite loop.

As you will tell I am not an expert, but I am already very happy with what I could accomplish, so please bare with me. From what I understand there is no counter indication as to using two for statements followed by each other. What would your suggestions be?

Tags:

2

Answers


  1. 0

    Instead of relying on the loop end condition that is i < caData.length you can have a break condition within the loop body. Something like:

    if(feuilleCA.getRange(i + 1, X).getValue() == "") break;
    where X is any cell which has data for each row. I remember facing this issue working with Microsoft Excel sheets in C#. Might work on google sheets as well.

    Login or Signup to reply.
  2. 0

    You don’t show your entire script so I had to make some assumption. First, that the the caData is the same size as the data you want to replace in feuilleCA. Second, that now is the current date. And lastly that only values are in feuilleCA columns L:Q.

    First I get caData. Then I only get the data from feuilleCA columns L:Q. I replace the data in fData and put it back.

    I’ve eliminated the need for 2 for loops. And I’ve eleminated the need for all the setValue() call by merging it into 1 setValues() call.

    function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let caData = spread.getSheetByName("Data").getDataRange().getValues();
    let feuilleCA = spread.getSheetByName("FeuilleCA");
    // assumed to be the same size as caData
    let fRange = feuilleCA.getRange(1,12,feuilleCA.getLastRow(),6); // L:Q
    let fData = fRange.getValues();
    let amount = 1;
    let mode = 2;
    let now = new Date();
    let ID = 3;
    // you don't show how tax and invoice is determined
    let invoice = 4;
    let tax = 5;

    caData.forEach( (row,index) => {
        if( ( row[3] === ID ) || ( row[1] === invoice ) ) {
          fData[index][2] = amount;
          fData[index][3] = mode;
          fData[index][4] = now;
        }
        else {
          fData[index][0] = amount;
          fData[index][1] = mode;
          fData[index][5] = now;
        }
      }
    );
    fRange.setValues(fData);
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

    Reference

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search