Недавно нашей компании понадобился коннектор, который будет брать данные из системы управления коллективной работы и загружать их в Гугл таблицы для последующей аналитики трудового дня сотрудника и бюджета проекта.
Было принято решение самим брать данные из системы без использования платных сервисов. Для этого мы использовали расширение Google Apps Script.
Я являюсь junior разработчиком, данная статья для тех, кому будет полезной следующая информация:
Как записать массив данных в таблицу?
В интернете не было информации или хотя бы намека, как мы можем построчно записать данные в таблицу Google Sheets из массива используя Apps Script.
Ставим задачу:
Google Tables Control Panel
Делаем запрос к серверу 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) } }
Когда получим массив данных с объектами, помним, что мы выгружаем задачи, а у каждой задачи, есть свои заголовки такие, как “наименование задачи”, “статус”, “наименование проекта”, “приоритет задачи” и т.д
Деструктуризируем данные и берем нам нужные свойства объекта
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: ‘Важно’}
]
Даже если мы добавим в наш объект новое свойство (новое поле задачи) - это никак не повлияет на наш следующий шаг, не нужно переписывать будет снова и снова функцию для записи данных в саму таблицу.
Для этого нам нужен цикл, где на каждой итерации, будем перебирать объекты задач и еще одна функция с циклом для обработки полей в задаче, поэтому нам потребуется дополнительная функция (в нашем случае этой функцией будет 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), 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)
}
}
Добавляем ссылку для запуска нашего алгоритма в панель задач (Создаем триггер)
Trigger Google Tables Control Panel
Так вот для данной задачи нам потребовалось сделать три шага. Пользуйтесь, экспериментируйте и развивайтесь.
Надеюсь информация была полезной, и у вас появятся свои идеи для реализации. В любом случае, каждый наш шаг и достижение, пусть даже маленькое, дает нам импульс к профессиональному росту.
habr.com
Было принято решение самим брать данные из системы без использования платных сервисов. Для этого мы использовали расширение Google Apps Script.
Я являюсь junior разработчиком, данная статья для тех, кому будет полезной следующая информация:
Как записать массив данных в таблицу?
В интернете не было информации или хотя бы намека, как мы можем построчно записать данные в таблицу Google Sheets из массива используя Apps Script.
Ставим задачу:
Реализация:
Шаг 1
- Заходим в Google таблицу, далее переходим в РАСШИРЕНИЯ
- В открывшейся вкладке выбираем Apps Script
Google Tables Control Panel
Делаем запрос к серверу 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), 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)
}
}
Добавляем ссылку для запуска нашего алгоритма в панель задач (Создаем триггер)
Trigger Google Tables Control Panel
Так вот для данной задачи нам потребовалось сделать три шага. Пользуйтесь, экспериментируйте и развивайтесь.
Надеюсь информация была полезной, и у вас появятся свои идеи для реализации. В любом случае, каждый наш шаг и достижение, пусть даже маленькое, дает нам импульс к профессиональному росту.
Как записать преобразованный массив данных в Google таблицу с использованием Javascript
Я бы хотела поделится своим опытом и рассказать, как помогает автоматизация рутинных задач с использованием Javascript и Google Apps Script. Возможно, это поможет многим для экономии рабочего времени...
habr.com