Пользовательская функция для Google Spreadsheet — обработка массива, переданного из электронной таблицы

Я пытаюсь создать простой скрипт для работы с электронной таблицей Google. Электронная таблица принимает данные из формы Google, поэтому в электронной таблице есть ряд значений, подобных этому:

http://i43.tinypic.com/2losg5.jpg

Моя цель состоит в том, чтобы написать скрипт, который бы удалял число из каждого ввода формы в указанном пользователем диапазоне, а затем добавлял все числа, чтобы получить единую оценку. Так, например, пользователь может ввести =sumColumns(H2:K2) в ячейку, и он вернет сумму баллов (для примера снимка экрана, который я разместил, он вернет результат 3+3+0+3, 9).

Вот код, который я написал для этого:

function sumColumns(values) {
  var sum = 0;
  for(var i = 0; i <= values.length; i++){
    var input = values[0][i];
    var x = input.toString();
    var y = x.charAt(0);
    var num = parseInt(y);
    sum += num;
  }
  return sum;
}

Проблема в том, что кажется, что он складывает только два значения вместе. Итак, когда я помещаю =sumColumns(H2:K2) в ячейку электронной таблицы, она возвращает только 6. Кроме того, в строке 3, если я изменю его с i <= values.length на i < values.length, он добавит только одно число, так что в результате я получу 3. Я предполагаю, что я неправильно понимаю, как значения электронной таблицы Google передаются в функцию, но я совершенно не смог заставить ее работать. Буду очень признателен за любую помощь!


google-sheets javascript google-apps-script google-forms custom-function
person WebTheaterGeek    schedule 01.08.2013    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Упс - отредактировал и сохранил вопрос, написал ответ - и забыл его сохранить. Я позволил Сержу опередить меня! И, как обычно, ответ Сержа работает хорошо (с целыми значениями). Но вы спросили о том, как все устроено, так что вот.

Когда вы указываете пользовательской функции диапазон в качестве параметра, в данном случае H2:K2, функция получает двумерный массив, эквивалентный возвращаемому значению Range.getValues(). Вы можете легко проверить это, (временно) изменив свою функцию, чтобы она возвращала JSON-представление параметра:

function sumColumns(values) {
  return JSON.stringify(values);   // For debugging, just return string showing values
  ...

Вот что вы увидите в ячейке, содержащей =sumColumns(H2:K2):

[["3 (Rarely)","3 (Frequently)","0 (Never)","3 (Frequently)"]]

Это показывает массив, заключенный в [ .. ], с другим массивом внутри, также заключенным в квадратные скобки, и этот массив состоит из четырех элементов. Если вместо этого мы изменим диапазон на H2:K3, мы получим это (с добавлением пробелов для ясности):

[
  ["3 (Rarely)","3 (Frequently)","0 (Never)","3 (Frequently)"],
  ["","","",""]
]

Теперь, когда вы это знаете, легко понять, почему ваша функция давала именно такие результаты.

Во-первых, for(var i = 0; i <= values.length; i++) использует неправильные границы массива для циклического обхода, поскольку values.length сообщит нам, сколько строк содержится в values. В H2:K2 эта длина равна 1. Вместо этого нам нужно перебирать столбцы в первой строке (values[0]) с ее 4 ячейками.

Вы задавались вопросом о < против <= для этого цикла - нам нужно использовать <, так как это индекс, основанный на 0, а .length возвращает количество элементов. Таким образом, мы получаем:

for (var i=0; i < values[0].length; i++){ ... }

Использование parseInt() является хорошим выбором и хорошо подходит для значений в вашей электронной таблице. Однако его можно улучшить, гарантируя, что любые строковые значения имеют начальные нечисловые значения, удаленные первыми - parseInt() может затем найти целое число внутри строки.

function sumColumns(values) {
  return JSON.stringify(values);   // For debugging, just return string showing values
  var sum = 0;
  for(var i = 0; i < values[0].length; i++){
    var input = new String(values[0][i])
                 .replace( /^\D+/g, '');    // Strip any leading non-digits
    sum += parseInt(input);
  }
  return sum;
}
person Mogsdad    schedule 09.09.2013

arrow_upward
0
arrow_downward

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

Комментарии курсивом:

Хммм, смущает... сначала я подумал, что это должен быть 2D-массив, но я зарегистрировал val[0] в своем тесте, и он вернул "неопределенную" ошибку... Должно быть, в тот момент я что-то опечатался. .. В любом случае, именно поэтому я искал способ обработки данных в виде строки и использования разделения и регулярных выражений. Как обычно с ответами Могсдада, у вас есть ответ и все объяснения, которые к нему прилагаются ;-) и, как часто бывает с ним, вы получаете лучший ответ, чем мой. (одно ограничение, хотя (@Mogsdad) ваш комментарий о нецелочисленных значениях также может быть применен к вашему коду... вы просто удаляете любое десятичное значение с помощью parseInt()...:-) Тем не менее, ваш вариант использования был хорошим описан, и в пределах этого примера оба кода должны работать должным образом, код Могсдада является более «академическим» и программно правильным.

конец комментария.

Используя этот прием ниже, он работает, как и ожидалось, для любого диапазона ввода (1 или более строк и столбцов):

function sumCol(val) { // returns the sum of all numeric values in range
  var values = val.toString().split(',');
  var sum = 0;
  for(var n=0;n<values.length;++n){
    sum+=Number(values[n].replace(/[^0-9+.]/ig,''));
  }
  return sum;
}

Я также изменил режим извлечения чисел, чтобы сделать его более универсальным.

person Serge insas    schedule 08.09.2013