Google Apps Script Reading and Writing Data from/to Sheets in a loop does not work

Question

I was trying to use Google Sheets as my inventory manager. Every time I got bulk orders, I run them through a for loop and decrease the corresponding stock from another sheet. So I'm using "setValue" function to write data to Sheets.

The problem here is, I'm reading the current stock in a loop and writing the decreased stock in the same loop. However, "setValue" doesn't seem to work until the loop fully ends.

For example,

Milk = 5
>>New orders arrived with this order (1 milk, 1 bread, 1 coffee, 1 milk)

So here I will have a loop of 4 for each item.

For index 0, I read current stock from stock sheet (5). Then I check new order quantity (1). So setValue(4). For index 1,... For index 2,... For index 3, I read current stock from the same stock sheet. It supposed to be 4, but it reads 5. The data supposed to be written 3 recursions before.

So the end result becomes;

Milk = 4

But it supposed to be

Milk = 3

Hope I was able to tell you the problem. So setValue function runs but it does not affect the corresponding sheet until the loop is over. That's why I cannot use it as a live database. Have you ever encountered a problem like this before?

Code looks like this

for(ordersArray){
  for(stocksArray){
    read stock from sheets
    calculate new stock quantity
    write new quantity with setValue //here this suppose to write on each loop but it writes only at the end of the loop
  }
}

As with any technology it takes a bit of experience and understanding in order to use it wisely. You are quite right that writing to a sheet and then reading from it immediately without an intervening SpreadsheetApp.flush() can lead to problems because the information may not be correct because it has not been given enough time for the spreadsheet to update itself. This is especially important in a server based technology. — Cooper
Perhaps it would be worth your time to take a look and Best Practices from Google Apps Scripts Support Page — Cooper
Can you post the full code you are referring to? The issue you are encountering is most likely related to the read stock from sheets part. Moreover, just like @Cooper has mentioned, you might want to take a look at the Best Practices documentation in Apps Script. — ale13

Cooper Cooper · Accepted Answer · 2020-06-14T23:00:37

I played around with this idea to see if I could easily reproduce the problems you are seeing with the way I tend to work on a spreadsheet. I have eight columns and 1000 rows of fictitious data and I performed this "=SQRT(POW(SUM(A2:H2),2))" calculation on each row. To make it more challenging I'm reloading the array of data with semi random numbers and immediately reading the data with no SpreadsheetApp.flush() and then after the flush reading again and then comparing the last twenty-one rows of sums to see if they are not the same.

function readandwriterealyfast() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()-1);
  let vs=rg.getValues();
  vs.forEach(function(r,i,A){
    let n=Math.floor(Math.random()*100);
    r.forEach(function(c,j){
       A[i][j]=i+n+1;
    });
  });
  rg.setValues(vs);
  let sums=sh.getRange(sh.getLastRow()-20,sh.getLastColumn(),21,1).getValues();
  SpreadsheetApp.flush();
  let sums2=sh.getRange(sh.getLastRow()-20,sh.getLastColumn(),21,1).getValues();
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(sums) + '<br />' + JSON.stringify(sums2)), "Sums");
  //=SQRT(POW(SUM(A2:H2),2))
}

And these are the sums in JSON format.

[[8080],[8048],[8376],[8008],[8136],[8024],[7880],[7920],[8048],[8328],[7992],[8528],[8344],[8336],[8312],[8128],[8328],[8424],[8072],[8680],[8688]]

[[8080],[8048],[8376],[8008],[8136],[8024],[7880],[7920],[8048],[8328],[7992],[8528],[8344],[8336],[8312],[8128],[8328],[8424],[8072],[8680],[8688]]

And I checked the data in the spreadsheet and it agrees with them. I'm not trying to say that it doesn't happen. But I find it remarkably fast when you tend to minimize the amount of cell functions utilized in your spreadsheets. And in my case I avoid the use of cell functions almost totally because I don't like them. (That's my personal opinion)

You might want to get a consultant to help you with your project. There may be something that can be done to provide your with a better outcome without too much additional effort.

Google Apps Script Reading and Writing Data from/to Sheets in a loop does not work

1 Answers