How to write converted data array to Google Spreadsheet using Javascript
I would like to share my experience and tell you how automating routine tasks using Javascript and Google Apps Script helps. Perhaps this will help many to save working time in the future for the HR department and project management managers.
Recently, our company needed a connector that will take data from the teamwork management system and upload it to Google spreadsheets for subsequent analytics of the employee’s work day and project budget.
It was decided to take data from the system ourselves without using paid services. We used the Google Apps Script extension for this.
I am a junior developer, this article is for those who will benefit from the following information:
How to write an array of data to a table?
There was no information on the Internet, or even a hint, how we can write data line by line to a Google Sheets table from an array using Apps Script.
We set the task:
We make a request to the server – Sort the data – Form a data array for writing – Write the converted data array to a table
Implementation:
Step 1
Go to Google spreadsheet, then go to EXTENSIONS
In the tab that opens, select Apps Script
Making a request to the REST API server. For a request to the server, we use the built-in class UrlFetchApp. Here I will not paint as for each service, there is documentation and rules for subsequent requests to the REST API server.
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) } }Step 2
Let’s transform the data array.
When we get a data array with objects, remember that we unload tasks, and each task has its own headlines such as “task name”, “status”, “project name”, “task priority”, etc.
Destructuring data and take the desired properties of the object
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
}We create a simple array with tasks and their fields for subsequent recording in a table. Each property of an object is data to be written to a cell, and each new object is a row in our table.
[
{ user: ‘Иван’, target: ‘service/tasks/123456789’}, name: ‘Написать статью’, endTime: ‘today’, status: ‘в работе’, priority: ‘Важно’},
{ user: ‘Маша’, target: ‘service/tasks/12345678’}, name: ‘Редактировать статью’, endTime: ‘today’, status: ‘в работе’, priority: ‘Важно’}
]
Step 3
The length of each object in the array ‘obj.length = cell’ (cell: 6) – is equal to the number of columns, and the length of the data array is equal to the number of rows ‘arr.length = row'(row: 27).
Even if we add a new property to our object (a new task field) – this will not affect our next step in any way, there will be no need to rewrite the function again and again to write data to the table itself.
To do this, we need a cycle, where at each iteration, we will iterate over the task objects and another function with a cycle for processing the fields in the task, so we need an additional function (in our case, this function will be setValuesInTable), which will take a row number, contain a counter for rows and columns, as well as a write method setValue and method getRange accepting the number of the column (cell) and row (row)
Script to write data to a table
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)
}
}Function for writing data to a table row by row
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])
}
}
Adding a link to run our algorithm to the taskbar (Creating a trigger)
So for this task, we needed to take three steps. Use, experiment and develop.
I hope the information was useful, and you will have your own ideas for implementation. In any case, our every step and achievement, even a small one, gives us an impetus to professional growth.
