Retrieve getDisplayValue() of specific cell in array produced by formula-CodePudding

Having read through several posts addressing the inability to use an apps script to retrieve a value produced by a formula, I suspect I'm just burning valuable daylight on this.

Via the script, I've retrieved an array of display values with getDisplayValues() and actual (I think) values with getValues(). Each array includes the entire column, when in actuality only cells 2-11 contain formula output.

Attempts to retrieve values from the array return NULL.

var SpreadsheetID = "myID";
var SheetName = "mySheet";


function gammaTilt() {
  var ss = SpreadsheetApp.openById(SpreadsheetID);
  var sheet = ss.getSheetByName(SheetName);
  var column = sheet.getRange("AH1:AH");

  var dispVal = column.getDisplayValues(); // returns array of full column, including display values
  var cellVal = column.getValues(); // returns array of full column, including actual (?) values

  var label = dispVal[0][0]; // returns column header, a string value
  var d = dispVal[11][0];    // returns null
  var a = cellVal[11][0];    // returns null
};

Column AH is dynamic in length, so the solution must find data range length, then the last value in the range. It appears, though, that apps script cannot find a range created by a formula?

It would be much more complex to do this, but is the only viable approach to develop an apps script that works with the original dataset, rather than spreadsheet manipulation of these data?

EDIT: Log output for cellVal

[21-09-14 13:02:33:500 PDT] Logging output too large. Truncating output.
[[All Date],
[Sun Aug 29 17:00:00 GMT-07:00 2021],
[Mon Aug 30 17:00:00 GMT-07:00 2021],
[Tue Aug 31 17:00:00 GMT-07:00 2021],
[Wed Sep 01 17:00:00 GMT-07:00 2021],
[Thu Sep 02 17:00:00 GMT-07:00 2021],
[Mon Sep 06 17:00:00 GMT-07:00 2021],
[Tue Sep 07 17:00:00 GMT-07:00 2021],
[Wed Sep 08 17:00:00 GMT-07:00 2021],
[Thu Sep 09 17:00:00 GMT-07:00 2021],
[Sun Sep 12 17:00:00 GMT-07:00 2021],
[Mon Sep 13 17:00:00 GMT-07:00 2021],

//The remainder of the array is empty cell values

The actual cell values from the sheet:

AH1    All Date
AH2    Mon Aug 30 2021
AH3    Tue Aug 31 2021
AH4    Wed Sep 1 2021
AH5    Thu Sep 2 2021
AH6    Fri Sep 3 2021
AH7    Tue Sep 7 2021
AH8    Wed Sep 8 2021
AH9    Thu Sep 9 2021
AH10   Fri Sep 10 2021
AH11   Mon Sep 13 2021
AH12   Tue Sep 14 2021

Any idea why the date strings are offset in the returned array?

CodePudding user response：

indices start at 0 in appScript (like most programming languages) So cellVal[11][0] is attempting to pull the value from cell AH12.

If you're looking for the value from cell AH11 that's cellVal[10][0]

also, .getValues() does not pull formulas so you're safe using that, you don't need to use .getDisplayValues()

CodePudding user response：

Try this:

var d = dispVal[11][0];
var a = cellVal[11][0];

Update

As for 'offset' in dates. I'm sure it has to do with time zone preferences in Spreadsheet settings.

Look, I've made the function that grabs your dates and converts them into date-objects:

function test_dates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A1:A11');
  var dates = range.getValues().map(x => [new Date(x[0])]); // strings --> objects
  range.offset(0, 1).setValues(dates);
}

If I set time zone GMT 00 I'm getting the 'offset':

If I set time zone GMT-07 the 'offset' disappears:

As for 'null' returns, sorry. No guess unless there is no reproducible piece of code dummy data. I'm tried your code with my data and it works just fine. It returns the value from the cell [11][0], no matter if the value was static or calculated by formula.