CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptrefreshcell
cFreed
cFreed

Reputation: 4484

Sheet-B cells late refreshed when depending on a sheet-A cell programmatically modified by apps-script

I have a sheet containing a database-like range named _database like this:
Database range

In a second sheet _A is a 2-cells range named _currentRow like this:
Criteria range where the here framed cell is named _currentRowName

A number of cells of _A use this _currentRow range as a criteria for DGET to extract data from _database, like this:

=DGET(_database; "Adresse - Nom"; _currentRow)

And finally is an apps-script which scans _database, and for each row prepares the corresponding document in _A like this:

  _A.getRange('_currentRowName').setValue(clientName);

(where clientName comes from the cell in Nom column of the current row)

This (described above) works perfectly, but...

There is a third sheet _B where a number of cells also use the _currentRow range as a criteria for DGET to extract data from _database, like this:

=DGET(_database; "Adresse - Nom"; _currentRow)

And another apps-script scans _database the same way as in the previous case, preparing the corresponding document in _B like this:

  _B.getRange('_currentRowName').setValue(clientName);

In this case, the result is that, for each row, _B document cells are populated, with the data corresponding to the _database row adressed by the previous clientName value stated in _currentRowName.
So it seems to act like if .setValue(clientName) did not take immediately effect... but was nevertheless finally effective, so the next row will use this previous value, and so on!

You can notice that the process is strictly identical to the first case, except that the involved cells (those that use DGET) are now in a distinct sheet than the _currentRow criteria.
I've wondered if, when in the context of an apps-script running, a sort of delay would take place for _B to be updated when _A changes.
So for test purpose I wrote a micro function like this:

function test() {
  _A.getRange('_currentRowName').setValue('<arbitrary value>');
  var result = _activeSpreadsheet.getSheetByName('_B').getRange(8, 3).getValue();
  // .getRange(8, 3) is a cell using the DGET data extraction
  ui.alert(result);
}

But no, it works like expected!

So I remain stuck: what can cause the error I reported above?

Upvotes: 0

Views: 31

Answers (0)

Related Questions