Как записать преобразованный массив данных в Google таблицу с использованием Javascript

Я бы хотела поделится своим опытом и рассказать, как помогает автоматизация рутинных задач с использованием Javascript и Google Apps Script. Возможно, это поможет многим для экономии рабочего времени в дальнейшем отделу HR и менеджерам управления проектов.

Недавно нашей компании понадобился коннектор, который будет брать данные из системы управления коллективной работы и загружать их в Гугл таблицы для последующей аналитики трудового дня сотрудника и бюджета проекта. 

Было принято решение самим брать данные из системы без использования платных сервисов. Для этого мы использовали расширение Google Apps Script.

Я являюсь junior разработчиком, данная статья для тех, кому будет полезной следующая информация:

Как записать массив данных в таблицу?

В интернете не было информации или хотя бы намека, как мы можем построчно записать данные в таблицу Google Sheets из массива используя Apps Script.

Ставим задачу:

Делаем запрос на сервер — Сортируем данные — Формируем массив данных для записи — Записываем преобразованный массив данных в таблицу 

Реализация:

Шаг 1

1. Заходим в Google таблицу, далее переходим в РАСШИРЕНИЯ 

2. В открывшейся вкладке выбираем Apps Script

Делаем запрос к серверу REST API.  Для запроса к серверу используем встроенный класс UrlFetchApp. Тут я не буду расписывать так, как для каждого сервиса, есть документация и правила для последующих запросов к серверу REST API.

const postTasks = (url, options) => {    try {      const response = UrlFetchApp.fetch(url, options)      if (response.getResponseCode() === 200) {        return JSON.parse(response.tasks)  }    } catch (err) { console.log(err.message) } }

Шаг 2

Преобразуем массив данных.

Когда получим массив данных с объектами, помним, что мы выгружаем задачи, а у каждой задачи, есть свои заголовки такие, как “наименование задачи”, “статус”, “наименование проекта”, “приоритет задачи” и т.д 

Деструктуризируем данные и берем нам нужные свойства объекта 

const createObject = async () => {     try {     const arrayTasks = await getListTasks()     const sortTasks = []       arrayTasks.forEach(item => {         const { id, name, endTime, status, assignees, fields } = item         sortTasks.push({           user: !assignees ? "" : Object.values(assignees.users),           target: `href/${id}`,           name: name,           endTime: !endTime ? "" : endTime,           status: !status ? "" : status,           priority:            getPriorityField(fields) || getWithOutPriorityField(fields),         })       })     } catch (err) {       console.log("Функция createObject, ошибка:", err.message)     }   return sortTasks   }

Создаем простой массив с задачами и с их полями для последующей записи в таблицу. Каждое свойство объекта — это данные для записи в ячейку, а каждый новый объект — это строка в нашей таблице.

[

{ user: ‘Иван’, target: ‘service/tasks/123456789’}, name: ‘Написать статью’, endTime: ‘today’, status: ‘в работе’, priority: ‘Важно’},

 { user: ‘Маша’, target: ‘service/tasks/12345678’}, name: ‘Редактировать статью’, endTime: ‘today’, status: ‘в работе’, priority: ‘Важно’}

]

Шаг 3

Длина каждого объекта в массиве ‘obj.length = cell’ (cell: 6) — равно количеству колонок, а длина массива данных равна количеству строк ‘arr.length = row’ (row: 27).  

Даже если мы добавим в наш объект новое свойство (новое поле задачи) — это никак не повлияет на наш следующий шаг, не нужно переписывать будет снова и снова функцию для записи данных в саму таблицу. 

Для этого нам нужен цикл, где на каждой итерации, будем перебирать объекты задач и еще одна функция с циклом для обработки полей в задаче, поэтому нам потребуется дополнительная функция  (в нашем случае этой функцией будет setValuesInTable), которая будет принимать номер строки, содержать в себе счетчик для строк и столбцов, а также метод записи setValue и метод getRange принимающий номер столбца (cell) и строки (row)

Скрипт для записи данных в таблицу

const getArrayFromResultTasks = (arr) => {     // Выбираю активную страницу в гугл таблице getActiveSheet()     const ss = SpreadsheetApp.getActiveSpreadsheet();     const sheet = ss.getActiveSheet();     for (let i = 0; i <= arr.length - 1; ++i) {         setValuesInTable(Object.values(arr[i]), sheet, i)     }  }

Функция для записи данных в таблицу построчно

const setValuesInTable = (arr, sheet, counter) => {    // счетчик для строки row = 2 (начинаем со второй строки) + counter (0 + i)    let row = 2 + counter;    for (let i = 0; i < arr.length; i++) {        // счетчик для столбца cell = 1 + i        let cell = 1 + i        // запись         sheet.getRange(row, cell).setValue(arr[i])    } } 

Добавляем ссылку для запуска нашего алгоритма в панель задач  (Создаем триггер)

Так вот для данной задачи нам потребовалось сделать три шага. Пользуйтесь, экспериментируйте и развивайтесь. 

Надеюсь информация была полезной, и у вас появятся свои идеи для реализации. В любом случае, каждый наш шаг и достижение, пусть даже маленькое, дает нам импульс к профессиональному росту.