У меня есть адреса электронной почты в первом столбце. Когда я пытаюсь получить последнюю строку этого столбца, функция возвращает только первые два значения. Вот ссылка на лист: google-apps-script google-sheets
gchristo234 5 Дек 2017 в 01:01
2 ответа
Лучший ответ
Нет, работает правильно. range.getLastRow() возвращает абсолютный индекс последняя строка диапазона . Ваша линия
var rangeLastRow = SpreadsheetApp.getActive().getSheetByName(‘Sheet1’).getRange(2, 1).getLastRow();
Возвращает индекс строки ячейки A2, который вы указали в getRange(2, 1) , поэтому ваш вызов range.getValues() получает значения диапазона A2: A3. т.е. ваш вызов range.getLastRow() возвращает 2, потому что ваш диапазон (строка 2, столбец 1), поэтому ваш вызов range.getValues() смотрит на диапазон (строка 2, столбец 1, 2 строки)
Вам нужна последняя строка данных на листе , которую вы получаете с помощью sheet.getLastRow() . На самом деле, почему бы не избавиться от var rangelastRow вообще? Следующее даст вам результат, который вам нужен:
Apps Script getLastRow getLastColumn getValue setValue Mov
function myFunction()
Обратите внимание, что вы действительно хотите использовать getLastRow() — 1 при установке количества строк в диапазоне, потому что ваш диапазон начинается со строки 2, а не со строки 1.
Dean Ransevycz 4 Дек 2017 в 22:32
Как насчет этой модификации?
Пункты модификации:
- getRange(2, 1).getLastRow() означает, что он должен получить последнюю строку для диапазона «a2». Итак, результат 2.
- Чтобы получить последнюю строку листа, используйте SpreadsheetApp.getActive().getSheetByName(‘Sheet1’).getLastRow()
- Третий параметр getRange(row, column, numRows) — это numRows . Если вы хотите получить данные «a2: a6», numRows будет rangeLastRow — 1 .
- По указанной выше причине getRange(2, 1, 2).getValues() извлекает только 2 ячейки.
Отраженный выше модифицированный сценарий выглядит следующим образом.
Измененный скрипт:
var sheet = SpreadsheetApp.getActive().getSheetByName(‘Sheet1’); var rangeLastRow = sheet.getLastRow(); var emails = sheet.getRange(2, 1, rangeLastRow — 1).getValues(); Logger.log(emails);
Ссылки :
Источник: question-it.com
Как писать скрипты, макросы и код в Google Scripts — часть 2
Продолжаем тему скриптов в гугле. В прошлой статье я описывал самые основы написания кода, сегодня же я бы хотел подробнее осветить такие штуки, как:
- Методы и свойства объектов;
- Условия и циклы.
Увы, пока еще достаточно много теории и мало практики, но без этого, опять же, увы, никуда.
Давайте приступим.
Методы и свойства объектов Google Scripts
Для тех, кто запамятовал, на данный момент наш код выглядит следующим образом:
Google Apps Script. Методы getLastRow, getLastColumn, clear. Скрипты Google Sheets Урок 4.
function myfunction()< var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист таблицы в соответствии с иерархией. В переменной sheet будет ссылка на наш лист /*————- —Комментарий— —————*/ var dataArray = sheet.getRange(«A1:D3»).getValues(); // Получаем значения ячеек A1:D3 в переменную dataArray var sum1 = dataArray[0][0] + dataArray[0][1] + dataArray[0][2] + dataArray[0][3]; var sum2 = dataArray[1][0] + dataArray[1][1] + dataArray[1][2] + dataArray[1][3]; var sum3 = dataArray[2][0] + dataArray[2][1] + dataArray[2][2] + dataArray[2][3]; var avg1 = sum1/4; var avg2 = sum2/4; var avg3 = sum3/4; var array1 = [1, 2, 3, 4, 5]; var array2 = [«1», «2», «3», «4», «5»]; var array2d = [ [1, 2, 3], [4, 5, 6], [7, 8, 9] ]; sheet.getRange(«E1:F3»).setValues([ [sum1, avg1], [sum2, avg2], [sum3, avg3] ]); >;
Как вы могли заметить, у нас есть конструкции вида SpreadsheetApp.getActiveSheet() или sheet.getRange().getValues , которые многим кажутся очень непонятными (да, это нормально).
Разберем обе конструкции подробнее.
У нас есть объект SpreadsheetApp — он представляет собой возможность работы с таблицами в целом и является родительским (головным, основным, классом верхнего уровня, называйте как хотите) для таблиц. В общем, если вы хотите работать с таблицами гугла, то вам нужен этот объект.
Любой объект у гугла имеет определенный синтаксис (правила написания) вида . , где.
- Метод — это функция, которая принадлежит данному объекту (например объект — человек, методы: ходить, говорить, думать, читать и тп. В общем, какие-то действия).
- Свойство — это характеристики (переменные) данного объекта (например объект — человек, свойства: имя, фамилия, дата рождения и тп).
При этом гугл не стал заморачиваться со свойствами и описал только методы (кроме некоторых объектов), чем сильно упростил работу нам 🙂
Полный список для каждого объекта можно найти (с примерами) на странице оф. документации (увы, она на английском).
Итак, возвращаясь к нашим примерам. У нас есть объект SpreadsheetApp для таблиц. Но мы же хотим работать не со всеми файлами таблиц гугла, а только с одним определенным (обычно, с тем, что у нас сейчас открыт). Как гугл поймет с каким именно файлом мы хотим работать? Нужно указать его идентификатор (у любого файла гугла есть идентификатор).
Как его узнать? Ну.. мы конечно можем узнать его каким-то способом и сообщить гуглу, но если глянуть в методы SpreadsheetApp , то у него есть один замечательный метод под названием getActiveSheet() . Он, грубо говоря, говорит гуглу: «Эй, чувак, я хочу узнать ссылку на текущую таблицу». И гугл дает нам идентификатор, поэтому важно записать его в какую-нибудь переменную, чтобы дальше не вызывать снова этот метод, а использовать уже готовую ссылку (как с ярлыками для папок в винде).
Так как метод — это функция, то любой метод оканчивается на круглые скобочки () . Эти скобочки как раз и указывают, что нам нужен метод, а не свойство. В таких скобочках для некоторых функций можно передавать значения (аргументы) для дальнейшей работы метода. Например, мы могли бы получить ссылку на текущую таблицу через метод openByUrl (), но это не очень удобно 🙂
Что касается второй конструкции ( sheet.getRange().getValues ), то здесь тоже самое, но мы вызываем метод getValues для объекта Range , который в свою очередь получаем, обращаясь к методу getRange объекта Sheet , на который ведет ссылка sheet (да, это может быть сложновато для восприятия). Напишем конструкцию иначе:
var dataRange = sheet.getRange(«A1:D3»); var dataArray = dataRange.getValues();
Если вернуться чуть раньше, то целиком мы получаем значения диапазона с помощью трех переменных:
var sheet = SpreadsheetApp.getActiveSheet(); var dataRange = sheet.getRange(«A1:D3»); var dataArray = dataRange.getValues();
Но синтаксис работает таким образом, что вызывая метод верхнего объекта, вы получается объект уровнем ниже, для которого можете сразу вызвать метод его уровня, который вернет объект уровнем еще ниже, для которого. и так до самого нижнего уровня. Таким образом, например, мы можем обойтись вообще одной переменной вместо трех, если переиначим следующим образом:
var dataArray = SpreadsheetApp.getActiveSheet().getRange(«A1:D3»).getValues();
Здесь мы обратились к родительскому классу SpreadsheetApp , потом мы обратились к классу ниже Sheet , затем к классу еще ниже Range и для этого класса уже вызвали метод getValues() .
Поначалу вам может быть сложновато сразу вызывать методы подклассов для упрощения некоторого кода, чтобы не плодить переменные. Поэтому можно использовать маленькую хитрость: сначала расписываете подробно, потом просто ужимаете, копируя нужные части. Как вы могли заметить с примером из трех переменных, я просто взял для последней переменной первый объект и присвоил ей все методы из правых частей:
С методами более менее разобрались, теперь займемся условиями и циклами.
Циклы и условия Google Scripts
Вот у нас есть такие строки кода:
var sum1 = dataArray[0][0] + dataArray[0][1] + dataArray[0][2] + dataArray[0][3]; var sum2 = dataArray[1][0] + dataArray[1][1] + dataArray[1][2] + dataArray[1][3]; var sum3 = dataArray[2][0] + dataArray[2][1] + dataArray[2][2] + dataArray[2][3];
Код вроде бы удобен, но что делать, если, например, у нас увеличится кол-во рядов или строк? Переписывать каждый раз заново код? Не очень удобно, не так ли? 🙂
Здесь-то нам и пригодятся циклы. Если кратно, то цикл — это повторение какой-то операции до тех пор, пока условия цикла не будут выполнены. Основных вариантов цикла существует целых два — for и while . Их различие в том, что цикл for выполняется до тех пор, пока переменная цикла не достигнет определенного значения, а цикл while будет выполняться до тех пор, пока не будет выполнено определенное действие. При этом с циклами стоит быть несколько осторожными, т.к. очень легко создать бесконечный цикл (который будет выполняться пока ему не надоест или пока не сработает тайм-аут).
Рассмотрим пару примеров:
for (i=0; i
Данные строчки означают следующее:
У нас есть переменная цикла i (т.н. счетчик), которая каждый шаг цикла (итерацию) увеличивается на 1 ( i++ ) и цикл будет длиться до тех пор, пока i не станет больше или равно 100 . При этом каждую итерацию у нас в переменную sum будет прибавляться значение i (т.е. после такого цикла sum будет равно 0+1+2+3+4+. +99 = 4950 ).
Аналогичный примеру выше цикл while :
while (i
Дословно означает «Пока i меньше 100 , делать следующее: прибавить к сумме значение i , затем увеличить i на 1 «.
С условиями все еще проще, конструкция условия выглядит таким образом:
if (условие) < действие >else if (условие 2)
Например, условие — если у вас 5 яблок, то увеличить их вдвое (вот бы так с деньгами, да?), иначе уменьшить их кол-во на 3 :
if (apple == 5) < apple += apple; >else
Итак, как с помощью этого нам упростить свой код?
Если приглядеться, то у нас довольно много повторяющихся строк (которые немного отличаются). У нас сейчас, по сути, для каждой строки по две переменные ( sum и avg ), учитывая, что строк три, получаем 6 переменных. Однако как мы можем обойтись всего двумя? Да очень просто, достаточно для каждой строки подсчитывать сумму и среднее значение, затем печатать его в соотв. ячейке и переходить к следующей строке.
Таким образом, мы обойдемся всего двумя переменными и при этом нам вообще неважно сколько строк использовать. Если рассматривать этот вопрос более детально, то что нам нужно сделать? Мы для каждой итерации ( 1 итерация = 1 строка) должны подсчитать сумму и среднее значение ячеек этой строки. Однако, если мы захотим использовать больше или меньше ячеек, чем сейчас, то как нам это дело унифицировать? Тут нужен второй цикл, который будет просматривать каждую ячейку данной строки.
Теперь другой вопрос — как мы будем менять наш диапазон (строк и колонок)? Ну. тут магии нет, давайте просто создадим две переменные, которые будут отвечать за ширину и высоту диапазона (две переменные поменять всяко проще, чем кучу строк):
var rows = 3; var cols = 4;
Т.е. у нас на данный момент три строчки ( rows ) по четыре значения в каждой ( cols ).
Прежде, чем создавать циклы, давайте несколько унифицируем эту строчку:
var dataArray = sheet.getRange(«A1:D3»).getValues();
Т.к. диапазон будет зависеть только от переменных rows и cols , то от явного указания области надо уходить. Ввиду того, что A1:D3 — это не очень удобное написание для нас, следует поискать более подходящий. Смотрим оф документацию и видим, ага, есть прямо то, что нам нужно:
Будем использовать метод getRange с явным указанием строк и колонок:
var dataArray = sheet.getRange(1, 1, rows, cols).getValues(); // getRange (ряд, колонка, кол-во рядов, кол-во колонок)
Ну а дальше добавляем циклы:
for (var i = 0; i < rows; i++)< // для каждой строки: var sum = 0; // обнуляем переменную var avg = 0; // обнуляем переменную for (var j = 0; j < cols; j++)< // Для каждой ячейки в этой строке sum += dataArray[i][j]; // добавить значение ячейки в сумму >// перейти к следующей ячейке в строке avg = sum/cols; // посчитать среднее sheet.getRange(i+1, cols+1, 1, 2).setValues([ [sum, avg] ]); // записать значения > // перейти к следующей строке
При этом, следует заметить, что мы используем другой формат записи getRange при записывании значений суммы и среднего. Т.к. у нас ячейка, в которую записывать, зависит от наших переменных rows , cols (если строк будет, например, 7 , то записывать сумму и среднее следует в 8 и 9 строку).
Также обратите внимание, что номер ряда обозначается переменной i+1 , а не i , т.к. нумерация рядов идет от единицы, а итерация идет с нуля (т.к. нумерация элементов массивов также идет с нуля).
Собираем наш код обратно и получаем такую конструкцию:
function myfunction() < var sheet = SpreadsheetApp.getActiveSheet(); // Ссылаемся на текущий лист таблицы в соответствии с иерархией. В переменной sheet будет ссылка на наш лист /*————- —Комментарий— —————*/ var rows = 3; // кол-во строк var cols = 4; // кол-во ячеек в строке var dataArray = sheet.getRange(1, 1, rows, cols).getValues(); // getRange (ряд, колонка, кол-во рядов, кол-во колонок) for (var i = 0; i < rows; i++)< // для каждой строки: var sum = 0; // обнуляем переменную var avg = 0; // обнуляем переменную for (var j = 0; j < cols; j++)< // Для каждой ячейки в этой строке sum += dataArray[i][j]; // добавить значение ячейки в сумму >// перейти к следующей ячейке в строке avg = sum/cols; // посчитать среднее sheet.getRange(i+1, cols+1, 1, 2).setValues([ [sum, avg] ]); // записать значения > // перейти к следующей строке >;
Теперь попробуйте увеличить кол-во строк и рядов, не забыв при этом поменять значения переменных rows и cols и запустите скрипт снова 😉
Стало куда удобней, но.. может можно еще уменьшить наши телодвижения? Программисты — это народ такой, ленивый, чем меньше надо менять значений, тем лучше 🙂
Теперь вместо того, чтобы добавлять каждый раз новые переменные для строк и ячеек, нам достаточно просто вручную указать размер области, откуда брать значения. Но! Можно и это не указывать, пусть система сама определяет эту область. Она же может видеть, где начинаются пустые ячейки? Ну ведь может, да?
Как нам это узнать?
Давайте опять обратимся к документации (я вообще рекомендую почаще к ней обращаться, там очень много полезной информации) и поищем какой-нибудь метод для класса sheet (т.к. нам нужно узнать есть ли какой метод для выявления последнего непустого ряда/колонки на всем листе). Метод должен, по идее, начинаться со слов get (ведь нам надо что-то получить от него). И.. о, такой метод есть, даже два:
Это методы getLastColumn() и getLastRow() . Они занимаются тем, что возвращают нам значение последнего непустого ряда или колонки. Теперь просто заменим строчки:
var rows = 3; // кол-во строк var cols = 4; // кол-во ячеек в строке
var rows = sheet.getLastRow(); // кол-во строк var cols = sheet.getLastColumn(); // кол-во ячеек в строке
И попробуем изменить наш диапазон, добавив или удалив строки/колонки.
Послесловие
Чтож, надеюсь кто-то до этого момента все таки дочитал 🙂 Как и всегда, ждем Ваших вопросов, дополнений и всего такого прочего, что может появится в результате прочтения данной статьи.
Пожалуй, в следующей части я опишу как все тоже самое выглядит в скриптах VBA -экселя.
P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.
P.S 2 : Прошлые части обитают здесь и здесь.
Мы в соц.сетях: ВКFacebookTwitter
Белов Андрей (Sonikelf) Заметки Сис.Админа [Sonikelf’s Project’s] Космодамианская наб., 32-34 Россия, Москва (916) 174-8226
Источник: sonikelf.ru
Getlastrow описание функции google
Что же такое массив? Массив это таблица значений, состоящая из строк и столбцов. То есть по сути лист Google Sheets с какой-либо информацией это и есть массив.
однако это массив на листе.
Нам же интересна возможность работы с массивом в скриптах.
ПОЛУЧЕНИЕ МАССИВА
Здесь все стандартно. Сначала получаем книгу, потом лист, потом необходимый диапазон на листе и потом уже (вот это сначала непривычно и несколько обескураживает) — значения в этом диапазоне
const ss = SpreadsheetApp.getActiveSpreadsheet(); // получаем активную книгу
const ws = ss.getSheetByName(‘Array’); // получаем лист по указанному имени
const myArray = ws.getDataRange().getValues(); // получаем диапазон значений и сами значения в этом диапазоне
return myArray; //вернем массив как результат выполнения функции (понадобится в дальнейшем)
Logger.log(myArray); // выводим диапазон значений в логгер (консоль)
Результат Логгера — массив массивов:
[[Имя, Фамилия, Возраст], [Гена, Крокодил, 37.0], [Дмитрий, Ржевский, 20.0], [Кристофер, Робин, 14.0], [Ослик, Иа, 18.0], [Винни, Пух, 44.0], [Джон, Константин, 35.0]]
Пошагово
Я мучился вопросом зачем надо вот это:
const ss = SpreadsheetApp.getActiveSpreadsheet();
Ведь я и так уже в приложении Google Sheets (SpreadsheetApp) и в текущей книге ( getActiveSpreadsheet())
Но ведь из книги можно получить доступ и к почте и к календарю, а значит редактору надо буквально объяснять что именно присваивается переменной.
Способы получения данных
Вообще, получать значения можно несколькими способами, но основных всего 2:
const myArray = ws.getDataRange().getValues();
const myArray = ws.getRange(1,1, ws.getLastRow(), ws.getLastColumn()).getValues();
где: (1— номер строки с какой начинается «захват»,1 — номер столбца с какой начинается «захват», ws.getLastRow() — последняя строка с какими либо данными, ws.getLastColumn() — последний столбец с какими-либо данными)
Допустим мы не хотим захватывать шапку [Имя, Фамилия, Возраст]. Тогда начинать стоит со второй строки:
const myArray = ws.getRange(2,1, ws.getLastRow(), ws.getLastColumn()).getValues();
в этом как раз основное отличие от getDataRange() , потому что он по сути берет «всё, что видит»
Если же нам нужен какой-то определенный кусок таблицы, а не вся она то, соответственно:
const myArray = ws.getRange(1,1, номер строки ВКЛЮЧИТЕЛЬНО, номер столбца ВКЛЮЧИТЕЛЬНО).getValues();
Элементы массива
Если совсем просто, то вот это — [Джон, Константин, 35.0] — массив, он обозначается квадратными скобками [] и в нем «Джон» это первый элемент массива, «Константин» — второй, 35.0 — третий.
Обратиться к элементу массива можно следующим образом:
myArray[0] //вернет текстовое значение «Джон»
myArray[1] //вернет текстовое значение «Константин»
myArray[2] //вернет число 35.0
А вот это — [[Джон, Константин, 35.0]] — массив в массиве, где [Джон, Константин, 35.0] — первый [0] элемент массива
Источник: dmitriizhuk.ru