Блог

Прочитав несколько сообщений, посвященных невозможности использования скрипта приложений для извлечения значения, полученного с помощью формулы, я подозреваю, что просто сжигаю ценный дневной свет на этом.

С помощью скрипта я извлек массив отображаемых значений с помощью getDisplayValues() и фактических (я думаю) значений с помощью getValues(). Каждый массив включает в себя весь столбец, в то время как на самом деле только ячейки 2-11 содержат выходные данные формулы.

Попытки извлечь значения из массива возвращают значение 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
};
 

Столбец AH динамичен по длине, поэтому решение должно найти длину диапазона данных, а затем последнее значение в диапазоне. Однако, похоже, что сценарий приложений не может найти диапазон, созданный формулой?

Это было бы намного сложнее сделать, но является ли единственным жизнеспособным подходом для разработки сценария приложений, который работает с исходным набором данных, а не с манипуляциями с этими данными в электронных таблицах?

ИЗМЕНИТЬ: Вывод журнала для 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
 

Фактические значения ячеек с листа:

 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
 

Есть идеи, почему строки даты смещены в возвращаемом массиве?

индексы начинаются с 0 в appScript (как и в большинстве языков программирования). Таким образом, cellVal[11][0] пытается извлечь значение из ячейки AH12.

Если вы ищете значение из ячейки AH11, это cellVal[10][0]

кроме того, .getValues() не извлекает формулы, поэтому вы можете использовать их в безопасности, вам не нужно использовать .getDisplayValues()

Попробуй это:

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

Обновить

Что касается «смещения» дат. Я уверен, что это связано с настройками часового пояса в настройках электронных таблиц.

Послушайте, я создал функцию, которая захватывает ваши даты и преобразует их в объекты дат:

 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);
}
 

Если я установлю часовой пояс GMT 00, я получу «смещение»:

Если я установлю часовой пояс GMT-07, «смещение» исчезнет:

Что касается возврата «null», извините. Никаких догадок, если только нет воспроизводимого фрагмента кода фиктивных данных. Я попробовал ваш код с моими данными, и он работает просто отлично. Он возвращает значение из ячейки [11][0], независимо от того, было ли значение статическим или рассчитано по формуле.