Cell value changed with setValue not being recognized by getDataRange().getValues()

Question

I am trying to have apps script set a value in a table and then on success go to the BOMOnSuccess function where it will reload the values of the modified table.

The problem I am having is it seems like the code continues on before the value is actually set, so the table is retrieved is not up to date and what is being shown in my HTML page is not accurate.

When I "refresh" the HTML page it is correct again. What I need to happen is upon entering the new value in the prompt, for the cell value to be set and that new value is reflected in the listbox.

I have tried all sorts of different pauses both in .gs (Utilities.sleep) and javascript. None of which seem to make a difference.

Some of the times it would actually pause the server from setting the value even if I have is sleep after the line that sets it. Other times if I get it to pause correctly it still has the old data.

I also tried SpreadsheetApp.flush after setValue, however it made no difference.

.gs:

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

function showForm() {
  var html = HtmlService.createTemplateFromFile('Form')
      .evaluate();
  html
      .setTitle('AP Pricing Form')
      .setWidth(600)
      .setHeight(500);
  var dialog = ui.showModalDialog(html, "AP Pricing App")
}

function getBom(){
  var sheet = ss.getSheets()[4]
  var vals = sheet.getDataRange().getValues();
  return vals
}

function updateMaterialQuantity(qty,prod,mat){
  var sheet = ss.getSheets()[4]
  var values = sheet.getDataRange().getValues();

  for(var i = 1; i<values.length; i++){
    if(values[i][0] == prod){
      if(values[i][1] == mat){
        sheet.getRange(i+1,3).setValue(qty);
        SpreadsheetApp.flush();
        return;
      }
    }
  }
}

 function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
};

js:

//get BOM from BOM table
function refreshBOM(prodOption){
  for (a in document.getElementById("productMaterials").options) { document.getElementById("productMaterials").options.remove(a); }
  prod = prodOption
  function filterBOM(array){
    var newBom = [];
    for(var i = 1; i<array.length; i++){
      if(array[i][0] == prod){
        newBom.push(array[i])
      }
    }
    return newBom;
  }
  
  function BOMOnSuccess(array){
    bom = array
    bom = filterBOM(bom);
    for(var i = 0; i < bom.length; i ++){
      var opt = document.createElement("option");
      opt.text = bom[i][2] + " - " + String(bom[i][1]);
      opt.text = opt.text.toUpperCase();
      opt.value = bom[i][1];
      document.getElementById("productMaterials").options.add(opt);
    }
  }
  google.script.run.withSuccessHandler(BOMOnSuccess).getBom();
}


//edit materials 
function editProductQty(material){
if(material != ""){
  var qty = prompt("Enter new quantity");
  var prod = document.getElementById("productsSelect").value
  google.script.run.withSuccessHandler(refreshBOM(prod)).updateMaterialQuantity(qty,prod,material);
}
else{
  alert("Please choose a product and material");
}

}

What is expected, is for editProductQty to run when a button is clicked. That will cause the user to enter the new value. Then updateMaterialQuantity will run.

This will go to .gs and the range will be found and the new value set. Then refreshBOM is run where it will get the newly updated range and the new value will be shown on the HTML page.

What is actually happening is when getBOM is called, it somehow is getting the old non-updated version of the table. I believe the problem is that the actual setting of the value is happening after the js code finishes for some reason but I am not sure.

Answer 1

Reason of issue:

I think that the reason of your issue is refreshBOM(prod) of google.script.run.withSuccessHandler(refreshBOM(prod)).updateMaterialQuantity(qty,prod,material).

When google.script.run.withSuccessHandler(refreshBOM(prod)).updateMaterialQuantity(qty,prod,material) is used, at first, refreshBOM(prod) is run, then, updateMaterialQuantity(qty,prod,material) is run. By this, the values are retrieved before the values are updated. On the other hand, I think that the Spreadsheet can be updated.

Solution:

In order to avoid this issue, please modify as follows. I think that there are 2 patterns for your situation. Please select one of them. And please think of this answer as just one of several answers.

Pattern 1:

In this pattern, only Javascript is modified.

From:

google.script.run.withSuccessHandler(refreshBOM(prod)).updateMaterialQuantity(qty,prod,material);

To:

google.script.run.withSuccessHandler(refreshBOM).withUserObject(prod).updateMaterialQuantity(qty,prod,material);

and

From:

function refreshBOM(prodOption){

To:

function refreshBOM(temp, prodOption){

Pattern 2:

In this pattern, Javascript and Google Apps Script are modified.

Javascript side

From:

google.script.run.withSuccessHandler(refreshBOM(prod)).updateMaterialQuantity(qty,prod,material);

To:

google.script.run.withSuccessHandler(refreshBOM).updateMaterialQuantity(qty,prod,material);

and

Google Apps Script side

From:

if(values[i][1] == mat){
  sheet.getRange(i+1,3).setValue(qty);
  SpreadsheetApp.flush();
  return;
}

To:

if(values[i][1] == mat){
  sheet.getRange(i+1,3).setValue(qty);
  return prod;
}

• In this case, I think that the updated values might be able to be retrieved at getBom(), even if flush() is not used.

Reference:

• Class google.script.run

If I misunderstood your question and this was not the result you want, I apologize.