Установить даты в строках электронной таблицы

Я пытаюсь заставить это работать быстрее, даже незначительно. Сейчас обновление 470 строк занимает около 90 секунд. Это работает, но я знаю, что есть способы получше. Я понимаю, что вызовы Spreadsheet API являются медленными, а пакетная обработка getRanges и setValues ​​могла бы ускорить процесс, но это не кажется очевидным, учитывая то краткое, что я знаю.

function setDates() {

var app = SpreadsheetApp;
var ssData = app.getActiveSpreadsheet().getSheetByName("Data");

//Calculates due dates based on entered dates in column D
  for (var i=4; i<=ssData.getLastRow(); i++)  {                                     //cycles through all 18 pilots
    for(var j=0; j<25; j++) {                                                       //cycles through an individuals' 25 training items
    var dateCompleted = new Date(ssData.getRange(i+j,4).getValue());                //date of training completed
    var interval = ssData.getRange(i+j,5).getValue();                               //interval in months between date complete and date due
    var monthCompleted = dateCompleted.getMonth();
    var yearCompleted = dateCompleted.getFullYear();

      if (!isNaN(parseFloat(yearCompleted)) && !isNaN(interval)) {                  //checks to see if vars year and interval are numbers
      var dateDue = new Date(dateCompleted.setMonth((monthCompleted + interval)));  //date of training due
      var yearDue = dateDue.getFullYear();
      var monthDue = dateDue.getMonth();
      dateDue = new Date(yearDue, monthDue+1, 0);
      ssData.getRange(i+j,6).setValue(new Date(dateDue));                           //sets dateDue in column F
      }
    }
    i = i + 25;
  }
}

Общий обзор

В целом код в порядке, и проблемы возникают из-за недостатка опыта, а не из-за плохого стиля или привычки.

Я не знаком с SpreadsheetApp, поэтому вам следует подождать, если кто-то добавит другой ответ, прежде чем вы примете этот ответ, если вы этого хотите.

Вместо того, чтобы отправлять этот ответ, я быстро взглянул на DOC для SpreadsheetApp (надеюсь, что получил правильный) и добавил к этому ответу внизу под заголовком «Производительность».

Очки обзора

• Осторожно с вмятинами. все блоки кода должны быть с отступом (если беспорядок с отступами вызван плохим форматированием фрагментов, игнорируйте эту строку)

• Когда используешь куда объявите их в верхней части функции.

• Использовать константы const для переменных, которые не меняются.

• Использовать i, j только для индексов или счетчиков, если вы обращаетесь к координатам, row, column, или же x, y используйте имена, соответствующие имени оси.

  В этом случае похоже, что вы индексируете только по индексу строки. В переписывании я использую имя переменной row. Затем счетчик вызвал i чтобы отсчитать 25 и пропустить пропущенный ряд каждые 26 рядов.

  BTW При использовании i, j в циклах соглашение — это i для внутреннего цикла, а j — для внешнего.

• Не рассчитывайте одно и то же значение много раз. Например, вы рассчитываете i + j три раза.

• Избегайте одноразовых переменных.

• Использовать пока петли предпочтительнее за циклы, особенно если вы вручную увеличиваете счетчик в блоке циклов.

  Циклы while обычно синтаксически чище и менее сложны под капотом (если вы используете let для объявления счетчика).

  Примечание что при использовании циклов while легко забыть добавить приращение, поэтому не забудьте увеличить / уменьшить

• Использовать операторы арифметического присваивания в этом случае + = (Дополнительное задание) i += 25 скорее, чем i = i + 25;

• Нет необходимости создавать новый объект Date каждый раз, когда вы вносите изменения.

Дата объект

В Дата объекты называют Date.getFullYear всегда возвращает Число как целое число.

Нет необходимости использовать parseFloat и результат !isNaN(parseFloat(yearCompleted)) всегда будет истинным и поэтому не требуется.

Далее переменная yearCompleted используется только в ненужном предикате и как таковой вообще не требуется.

Чтобы установить дату в первый день месяца, вам не нужно использовать year, month + 1, 0 просто установите его на 1-й year, month, 1

Внутренний if утверждение

Неясно, если звонок получить interval = ssData.getRange(i + j, 5).getValue() вернет число или нет.

Поскольку использование monthCompleted зависит от действительной численности interval вы можете объединить два в одном выражении. Однако я подозреваю, что interval всегда будет числом, и поэтому нет необходимости в isNaN тест.

Переписать

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

function setDates() {
    var row = 4, i;
    const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
    while (row < data.getLastRow()) {
        i = 25;
        while (i--) {
            const interval = data.getRange(row, 5).getValue();
            if (!isNaN(interval)) {
                const completed = new Date(data.getRange(row, 4).getValue()); 
                completed.setMonth(interval + completed.getMonth());
                completed.setDate(1);
                data.getRange(row, 6).setValue(completed); 
            }
            row ++;
        }
        row ++;
    }
}

Спектакль

Я бегло просмотрел документацию SpreadsheetApp и, таким образом, решу проблемы с производительностью, которые вас беспокоят.

Проблема, я думаю, в использовании getRange (строка, столбец) который вы используете для получения каждой ячейки.

Скорее…

• Использовать getRange (строка, столбец, numRows, numColumns) чтобы получить все строки и столбцы за один вызов перед повторением ячеек.

• Затем получите массив значений из диапазона, используя range.getValues ​​() внести необходимые изменения.

• Затем используйте range.setvalues ​​() установить внесенные изменения

Это может значительно повысить производительность.

Переписать на основе Документов

Поскольку у меня нет данных или доступа к API, перезапись — это только предположение, основанное на быстром чтении DOC.

Единственная проблема, которую я могу предвидеть, — это то, почему вы пропускаете каждую 26-ю строку и почему вы начинаете с 5-й строки (индекс как строка 4).

function setDates() {
    var rowIdx = 0;
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
    const range = sheet.getRange(4, 4, sheet.getLastRow() - 4, 3);
    const data = range.getValues(); // as 2D array of rows by columns eg...
                                    // data[0][0] is row 4 column 4
                                    // data[10][2] is row 14 column 6
    for (const row of data) {
        if (rowIdx > 0 && (rowIdx % 26)) { // skip every 26th row
            const interval = row[1];
            if (!isNaN(interval)) {
                row[2] = new Date(row[0]);
                row[2].setMonth(interval + row[2].getMonth());
                row[2].setDate(1);
            }
        }
        rowIdx++;
    }
    range.setValues(data);
}