Платформа Google Apps Script действительно потрясающая, поскольку она помогает — как новичкам, так и экспертам — использовать возможности сервисов Google (а также внешних) для их повседневных уловок, а иногда даже для интеграции на уровне предприятия. SpreadsheetApp — одна из самых известных его функций, которая позволяет создавать и управлять документами электронных таблиц Google с помощью простых вызовов JS.
Как бы просто это ни выглядело, неправильное использование SpreadsheetApp может легко привести к тайм-аутам выполнения и быстрому исчерпанию вашей ежедневной квоты времени выполнения (что весьма ценно, особенно когда вы пользуетесь бесплатным планом). Это связано с тем, что для выполнения большинства операций SpreadsheetApp требуется значительное время (возможно, из-за того, что они внутренне сводятся к вызовам API Google? IDK), часто независимо от объема данных, считываемых / записываемых в каждом вызове.
В нескольких моих проектах, где огромное количество результатов приходилось сбрасывать в GSheets таким образом, я сталкивался с непроходимым временным барьером: независимо от того, насколько я оптимизировал, сценарии продолжали снимать сверх 5-минутного ограничения. Мне приходилось вносить в изображение кэширование в памяти, сначала для каждой строки, затем для каждого набора логических строк и, наконец, для всей таблицы (в этот момент задержки практически исчезли).
|
01
02
03
04
05
06
07
08
09
10
11
|
matrix = []; ... if (!matrix[row]) { matrix[row] = new Array(colCount); } for (k = 0; k < cols.length; k++) { matrix[row][k] = cols[k]; } ... sheet.getRange(2, 2, rowCount, colCount).setValues(matrix); |
Затем недавно мне довелось столкнуться с задачей рефакторинга скрипта GSheetd, написанного другим разработчиком. На этот раз это была другая история, так как каждая ячейка была названа по имени:
|
1
2
3
4
5
|
for (i = 0; i < data.length; i++){ spreadsheet.getRange("A" + (i + 2)).setValue((i + 2) % data.length); spreadsheet.getRange("B" + (i + 2)).setValue(data[i].sum); ... } |
И было слишком много ссылок, которые нужно было исправить вручную, и слишком много данных времени выполнения, чтобы использовать вызовы SpreadsheetApp по умолчанию, не превышая тайм-аут.
Тогда у меня возникла идея, почему я не могу иметь оболочку в памяти для SpreadsheetApp , которая дала бы нам преимущество в скорости без необходимости изменять существующий код?
Поэтому я написал свое собственное MemsheetApp котором используется простой двумерный массив в памяти для имитации электронной таблицы, без записи каждой операции в API.
Одна проблема, с которой я столкнулся, заключалась в том, что не существует определенного способа (вызова или события) для «сброса» данных, накопленных в памяти, при сохранении совместимости с API SpreadsheetApp . Лучшей вещью, которую я смог найти, был SpreadsheetApp.flush() который при обычном использовании сбрасывал бы данные всех открытых электронных таблиц. В моем случае мне пришлось явно сохранять ссылки на все экземпляры MemsheetApp созданные с помощью моего приложения, и MemsheetApp.flush() их все во время глобального MemsheetApp.flush() .
Итак, вот MemsheetApp (надеюсь, скоро я сделаю это GitHub):
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
MemsheetApp = { list: [], create: function(_name) { sheet = { //sheet: SpreadsheetApp.create(_name), name: _name, rows: [], maxRow: 0, maxCol: 0, getId: function() { return this.sheet.getId(); }, getRange: function(col, row) { if (!row) { row = col.substring(1); col = col.substring(0, 1); } if (isNaN(row)) { throw new Error("Multicell ranges not supported unless separating col and row in separate parameters"); } c = col; if (typeof col === "string"){ c = col.charCodeAt(0) - 65; // this supports 2 letters in col if (col.length > 1) { //"AB": 1 * (26) + 1 = 27 c = ( (c + 1) * ("Z".charCodeAt(0) - 64)) + (col.charCodeAt(1) - 65); } } if (this.maxCol < c) { this.maxCol = c; } r = parseInt(row) - 1; if (this.maxRow < r) { this.maxRow = r; } if (!this.rows[r]) { this.rows[r] = []; } if (!this.rows[r]) { this.rows[r] = 0; } return { rows: this.rows, getValue: function() { return this.rows[r]; }, setValue: function(value) { this.rows[r] = value; } } } }; this.list.push(sheet); return sheet; }, flush: function() { for (i in this.list) { l = this.list[i]; rowDiff = l.rows.length - Object.keys(l.rows).length; if (rowDiff > 0) { // insert empty rows at missing row entries emptyRow = []; for (c = 0; c < l.rows[0].length; c++) { emptyRow.push(""); } for (j = 0; j < l.rows.length && rowDiff > 0; j++) { if (!l.rows[j]) { l.rows[j] = emptyRow; rowDiff--; } } } l.sheet.getActiveSheet().getRange(1, 1, l.maxRow + 1, l.maxCol + 1).setValues(l.rows); } }} |
Как вы можете заметить, он предлагает чрезвычайно getValue() версию API SpreadsheetApp , в настоящее время поддерживающую только getValue() , setValue() и setNumberFormat() Range и create() и flush() SpreadsheetApp . Можно просто добавить новые функции, создав реализации (или оболочки) для дополнительных методов в соответствующих местах в иерархии возвращаемых объектов.
Если вы надеетесь использовать MemsheetApp в своем собственном проекте Apps Script, все, что вам нужно сделать, это убедиться, что вы MemsheetApp.flush() только закончили вставлять свои данные. Этот метод также безопасен для вызова обычного модуля SpreadsheetApp , что означает, что вы можете преобразовать свой существующий код на основе SpreadsheetApp чтобы он был совместим только с одной дополнительной безвредной строкой кода.
Тем не менее, самая крутая вещь в том, что вы можете переключаться между SpreadsheetApp и MemsheetApp только вы соответствующим образом MemsheetApp код:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SheetApp = MemsheetApp;// uncomment next line to switch back to SpreadsheetApp// SheetApp = SpreadsheetApp;// "SpreadsheetApp" in implementation code has been replaced with "SheetApp"var ss1 = SheetApp.create("book1").getActiveSheet();ss1.getRange(2, 2, 10, 3).setNumberFormat(".00");ss1.getRange("A2").setValue(10);...var ss2 = SheetApp.create("book2").getActiveSheet();ss2.getRange(2, 1, 1000, 1).setNumberFormat("yyyy-MM-dd");ss2.getRange(2, 2, 1000, 1).setNumberFormat(".0");// assume "inputs" is a grid of data, with dates in first column// and 1-decimal-place precision numbers in second columninputs.forEach(function(value, index) { ss2.getRange("A" + (index + 1)).setValue(value[0]); ss2.getRange("B" + (index + 1)).setValue(value[1]);});...// this will push cached data to "ss1" and "ss2", from respective in-memory grids;// and will have a similar effect (flushing all pending changes) when SpreadsheetApp is in useSheetApp.flush(); |
MemsheetApp — это далеко не полноценная оболочка, поэтому не стесняйтесь улучшать ее по своему усмотрению; и поделиться им здесь или где-нибудь публично в интересах сообщества скриптов приложений.
| Посмотрите оригинальную статью здесь: Побить часы ГАЗА: Передайте привет MemsheetApp!
Мнения, высказанные участниками Java Code Geeks, являются их собственными. |