Сценарий Excel, запускаемый из Power Automate, завершается с ошибкой 504

Question

Я использую PowerAutomate для добавления данных в отчет каждый день. Данные поступают по электронной почте в виде вложения CSV, они анализируются функцией Azure в 2D-массив и преобразовываются в JSON, затем передаются в сценарий Excel, который анализирует JSON, усекает массивы строк до нужной ширины, добавляет формулы в массивы строк для вычисляемых столбцов и использует table.addRows() функция для добавления данных в таблицу.

Это происходит на трех разных таблицах из трех разных отчетов, которые поступают (с интервалом в 30 минут).

• Один маленький, добавляя ~10 строк в день, без добавления столбцов формул, это всегда хорошо и успешно в PowerAutomate.
• Второй - ~150 строк в день с одним добавленным столбцом формул. При этом всегда удается добавить данные, но иногда соединитель PowerAutomate не работает с 504 BadGateway - истекло время ожидания запроса к API-графику.
• Третий - ~150 строк в день с несколькими добавленными столбцами формул. Обычно это не удается: либо успешно добавляется дата, но возвращается указанная выше ошибка, либо не удается добавить данные и возвращается. Мы не смогли запустить скрипт. Пожалуйста, попробуйте еще раз. Ошибка Office JS: строка 44: таблицы addRows: запрос завершился неудачно с кодом состояния 504, код ошибки UnknownError

Вот сама функция (в частности, третий пример):

      function main(workbook: ExcelScript.Workbook, inputData: string) {

  // Get formula columns as array of strings
  const additionalData = workbook.getWorksheet(`contact_time`).getRange("W2:AG2").getFormulas()[0]

  // Parse inputData to get actual array
  const dataToAdd: Array<Array<string>> = JSON.parse(inputData)

  // Remove first row's data as is just headers
  dataToAdd.shift()

  if (dataToAdd.length == 0) {
    return
  }

  // Truncate each input array row and add formula array columns on to the end
  for (const row of dataToAdd) {
    row.length = 22
    for (const column of additionalData) {
      row.push(column)
    }
  }

  // Add data to table
  workbook.getTable("t").addRows(null, dataToAdd)
}

Чтобы попытаться оптимизировать это, у меня есть

• в скрипте обращался к книге минимально возможное количество раз для меньшего количества запросов
• удалил звонок .getFormulas() и вместо этого жестко запрограммировал массив
• изменил тайм-аут PowerAutomate на P1D, а повторных попыток нет
• отключил пересчет для книги

Это работает в 100% случаев, если я вставляю JSON в функцию и запускаю ее в браузере. Я не могу понять, происходит ли это из-за фактического тайм-аута функции, PowerAutomate не может достаточно долго ждать результата, некоторого тайм-аута в Graph API или чего-то еще.

Размер книги меньше 5 МБ, таблица, которая чаще всего дает сбой, содержит около 13000 строк.

Редактировать:

В книге, которую я добавляю, есть рабочие листы для этих трех таблиц, а также еще несколько с таблицами анализа. Я также тестировал удаление разных листов, ссылок и т. Д. Замедление, похоже, связано с столбцами формул, которые уже существуют в таблице, поскольку только полное удаление этих столбцов приводило к успешной функции во всех случаях. Теперь вместо тайм-аута через 90 секунд он завершается за 8 секунд без каких-либо проблем. Почему это повлияет на скорость при отключенном пересчете?

Answer 1

Вероятно, это происходит из-за того, что таблица выполняет автоматический пересчет при добавлении новой строки в таблицу. График может истекать по таймауту при выполнении этого для таблицы с приличным размером. Мы можем отделить операции пересчета от операций с таблицами, установив для параметра вычисления значение «Вручную», а затем вернувшись к «Автоматически» после завершения операций с таблицами, чтобы инициировать пересчет.

      // At the beginning of the script right after main() function,
...
// Set calculation mode to manual
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
// do your processing...
// Set calculation mode back to automatic
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);   

Answer 2

Из дальнейшего тестирования кажется, что даже с ручным расчетом любая таблица с формулами в ней вызывает замедление при использовании table.addRows(), и ни Graph API, ни коннектор PowerAutomate не могут достаточно долго ждать ответа.

Единственное решение, которое я нашел, - это минимизировать или исключить формулы, которые

• находятся в таблице, добавляемой к
• ссылка на таблицу, добавляемую в

Когда я вставил формулы в таблицу проблем как значения и снова попытался добавить строки, в первый раз все заработало без каких-либо изменений в остальном.

В моем случае мне нужны вычисляемые столбцы в моей таблице напрямую, поэтому я периодически заменяю формулы, добавляемые скриптом, значениями. Это можно сделать вручную или с помощью сценария, используя range.setFormulas(range.getValues()) в указанном вами диапазоне (я рекомендую ограничить этот диапазон тем, что вам нужно, поскольку даже в браузере он завершил только около 1000 строк до истечения времени ожидания, поэтому вы не должны использовать его для всей таблицы).