When do values get written to a Google Sheet cell?

0
votes

I'm experiencing a weird issue. When I type text in a Google Sheet cell, then run a custom script (below) to get those values, it doesn't get the values I just type in. The values only become available once I move away from that cell (tab or enter).

var data = dataRange.getValues();

I'm not sure when the values actually get written to the cell. If I script a move away from that cell (below), I get the same issue.

var newRange = sheet.getRange("C4"); sheet.setCurrentCell(newRange);

Is there a way to write all the data to the sheet?

Can test using this code. Write some text to A1, run custom script and check the logs. If you don't move away from A1 before running script, logs will be empty.

function moveTest(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var newRange = sheet.getRange("A2");
  sheet.setCurrentCell(newRange);
  var cellOfInterest = sheet.getRange("A1").getValue();
  Logger.log(cellOfInterest);
}

Edit: same issue with setActiveSelection instead of setCurrentCell

2
google-apps-scriptgoogle-sheets
From my experience they're written as soon as the cell looses focus. So when I run the function function runOne(){ SpreadsheetApp.getActive().toast(SpreadsheetApp.getCurrentCell().getValue()); } I see the value in the toast immediately because the cell has lost focus when I run the function. – Cooper
Does this answer your question? In Google Spreadsheet using script, how to force flush a user's input? – Rubén
I ended up just removing the 'button' on the sheet and placing the script call in a custom menu. It's a round about way of solving the issue. I still think Google should implement a way for a script to force write the data to the sheet. I've tried a bunch of solutions, but most have pretty obvious corner cases which isn't ideal. – Craig.Pearce

2 Answers

2
votes

When do values get written to a Google Sheet cell?

When you press tab, arrow keys, enter or any other method that takes the focus away from the cell, the value gets written or committed.

0
votes

I'm not sure if it would work for you, but if you are checking for 1 exact cell, an if statement checking if the input is recognized before running the script would be alright:


    function checkwritten(){
      var app = SpreadsheetApp;
      var ss = app.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var ui = SpreadsheetApp.getUi(); 
      var InterestCell = sheet.getRange("A1");
      var cellOfInterest = sheet.getRange("A1").getValue();

      if (InterestCell.isBlank()) {
        Logger.log("missing value");
        ui.alert('There are missing values');
      }
      else {
        Logger.log(cellOfInterest);
        /*
        enter code here
        */
      }
    }

The other solutions proposed, such as making the button a menu option, or greying out the button until all entry is filled is also viable.

If you are checking for a range, the following two options are viable:

Option 1: create a cell with countblank in your sheet and it would be the cell of interest.

function checkwritten(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var InterestCell = sheet.getRange("A6");
  var cellOfInterest = sheet.getRange("A6").getValue();

  if (cellOfInterest == 0) {
    Logger.log("success");
  }
  else {
    Logger.log(cellOfInterest);
    /*
    enter code here
    */

  }

}

Option 2 - allow the script to ensure that there is no blank cells before running.

function checkwritten(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var CellCheckCount = 100; // 1 above the total number of rows to check
  var ui = SpreadsheetApp.getUi();
  var BlankCellCount = 0;
  var CheckCell = null;

  for (var i = 1; i < CellCheckCount; i++){
    CheckCell = sheet.getRange(i,1); //currently set to check column 1 up till 100-1 row
    if (CheckCell.isBlank()){
        BlankCellCount++;
        }
    else{
        }
  }

  if (BlankCellCount == 0) {
    Logger.log("success");
    /*
    enter code here
    */
  }
  else {
    Logger.log("fail" + BlankCellCount);
  }
}