Статьи

Побеждая часы ГАЗ: Скажи привет MemsheetApp!

Платформа 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 column
inputs.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 use
SheetApp.flush();

MemsheetApp — это далеко не полноценная оболочка, поэтому не стесняйтесь улучшать ее по своему усмотрению; и поделиться им здесь или где-нибудь публично в интересах сообщества скриптов приложений.

Посмотрите оригинальную статью здесь: Побить часы ГАЗА: Передайте привет MemsheetApp!

Мнения, высказанные участниками Java Code Geeks, являются их собственными.