Облачная функция для автоматизации импорта данных CSV в Google Sheets

1. Введение

Цель этого практического занятия — научить вас писать облачную функцию , которая реагирует на загрузку CSV-файла в Cloud Storage , считывает его содержимое и использует его для обновления таблицы Google Sheets с помощью API Sheets .

Это можно рассматривать как автоматизацию этапа «импорт в формате CSV», который в противном случае выполнялся бы вручную. Это позволит анализировать данные (возможно, созданные другой командой) в электронной таблице сразу после их получения.

Вот как выглядит реализация:

2. Настройка и требования

Настройка среды для самостоятельного обучения

1. Войдите в Cloud Console и создайте новый проект или используйте существующий. (Если у вас еще нет учетной записи Gmail или G Suite, вам необходимо ее создать .)

Примечание: Вы можете легко получить доступ к Cloud Console, запомнив ее URL-адрес: console.cloud.google.com.

Запомните идентификатор проекта (Project ID) — уникальное имя для всех проектов Google Cloud (указанное выше имя уже занято и вам не подойдёт, извините!). В дальнейшем в этом практическом занятии оно будет обозначаться как PROJECT_ID .

Примечание: Если вы используете учетную запись Gmail, вы можете оставить значение по умолчанию « Без организации» . Если вы используете учетную запись G Suite, выберите местоположение, которое подходит для вашей организации.

2. Далее вам потребуется включить оплату в Cloud Console, чтобы использовать ресурсы Google Cloud.

Выполнение этого практического задания не должно стоить дорого, если вообще что-либо. Обязательно следуйте инструкциям в разделе «Очистка», где указано, как отключить ресурсы, чтобы избежать дополнительных расходов после завершения этого урока. Новые пользователи Google Cloud имеют право на бесплатную пробную версию стоимостью 300 долларов США .

3. Создайте и настройте таблицу Google Sheets и включите API.

Для начала создадим новый документ в Google Sheets (этот лист может принадлежать любому пользователю). После создания запомните его идентификатор; он будет использоваться в качестве переменной окружения для функции, которую мы напишем:

В консоли GCP включите API Google Sheets в своем новом проекте, перейдя в раздел «API и сервисы», а затем в раздел «Библиотека API»:

В разделе «IAM и администрирование» перейдите к «Сервисные учетные записи» и запишите адрес электронной почты для учетной записи службы App Engine по умолчанию. Он должен иметь формат [e-mail] . Конечно, вы также можете создать собственную учетную запись службы, предназначенную специально для этой задачи.

Наконец, просто предоставьте этой учетной записи службы права на редактирование вашей электронной таблицы, используя кнопку «Поделиться»:

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

4. Создайте хранилище (bucket).

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

В консоли воспользуйтесь меню слева, чтобы перейти к разделу «Хранилище»... :

... и создайте новый сегмент с именем csv2sheet-POSTFIX (замените POSTFIX на уникальное значение), установив для всех остальных параметров значения по умолчанию:

Примечание: вы можете выбрать собственное местоположение, тип местоположения и класс хранения. Это не должно повлиять на остальную часть практического задания. В случае сомнений, просто используйте значения по умолчанию.

5. Создайте облачную функцию.

Теперь мы можем создать облачную функцию под названием csv2sheet , которая запускается при загрузке файлов в определенный сегмент Cloud Storage. Код будет написан на Node.js 8 с использованием асинхронных функций и встроенного редактора прямо в Cloud Console:

Обязательно установите в качестве триггера "Облачное хранилище" и измените имя корзины на то, которое вы создали на предыдущем шаге.

Также обновите точку входа для функции, которую мы собираемся использовать для вызова csv2sheet :

Теперь измените тело функции следующим образом:

1. используйте API облачного хранилища и электронных таблиц.
2. пометить функцию csv2sheet как async
3. Получите fileName из метаданных события Cloud Storage и определите имя для нового листа, который мы будем создавать:

const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")

exports.csv2sheet = async (data, context) => {
  var fileName = data.name;
  // basic check that this is a *.csv file, etc...
  if (!fileName.endsWith(".csv")) {
    console.log("Not a .csv file, ignoring.");
    return;
  }
  // define name of new sheet  
  const sheetName = fileName.slice(0, -4);

  // TODO!
};

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

При создании этой функции необходимо выбрать несколько важных параметров (нажмите на ссылку «Подробнее» внизу экрана):

• Воспользуйтесь раскрывающимся списком, чтобы выбрать учетную запись службы, описанную выше.
• Создайте переменную среды с именем SPREADSHEET_ID , которая должна соответствовать созданному вами ранее документу таблицы:

В качестве заключительного этапа настройки, вот содержимое package.json с указанием зависимостей от Cloud Storage и Google Sheets (используйте вкладку PACKAGE.JSON во встроенном редакторе консоли):

{
    "name": "csv2sheet",
    "version": "0.0.42",
    "dependencies": {
        "googleapis": "^51.0.0",
        "@google-cloud/storage": "^5.0.1"
    }
}

После того, как вы всё настроите, как описано, нажмите «Создать»! Через минуту ваша функция будет создана и развернута.

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

6. Настройка аутентификации и API Google Sheets.

Прежде чем писать дальнейший код в нашей функции Cloud с помощью встроенного редактора, нам необходимо заблокировать создание клиентского API Google с соответствующими областями действия Storage и Sheet (помните, это часть async функции).

В редакторе функций консоли нажмите кнопку "РЕДАКТИРОВАТЬ" и добавьте следующий код в тело вашей функции csv2sheet :

// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
  scopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/devstorage.read_only"
  ]
});

Отсюда мы можем создать клиент Sheets API:

const sheetsAPI = google.sheets({version: 'v4', auth});

7. Используйте API Google Sheets для создания пустого листа.

С помощью клиента Sheets API мы можем создать простую новую таблицу в нашем документе, но прежде чем продолжить, давайте кратко рассмотрим терминологию:

• Электронная таблица представляет собой сам документ и ссылается на него по его идентификатору (обсуждавшемуся выше и видимому в URL-адресе документа).
• Лист — это одна из вкладок в документе, и на него можно ссылаться по его имени (названию вкладки) или по идентификатору, сгенерированному при создании листа.

Учитывая это, вот функция, использующая клиент Sheets API для создания пустого листа на позиции 2 (обычно после стандартного "Sheet1"), с 26 столбцами, 2000 строками, при этом первая строка зафиксирована (добавьте её в свою функцию с помощью встроенного редактора):

function addEmptySheet(sheetsAPI, sheetName) {
  return new Promise((resolve, reject) => {
    const emptySheetParams = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            addSheet: {
              properties: {
                title: sheetName,
                index: 1,
                gridProperties: {
                  rowCount: 2000,
                  columnCount: 26,
                  frozenRowCount: 1
                }
              }
            }
          }
        ]
      }
    };
    sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
        if (err) {
          reject("The Sheets API returned an error: " + err);
        } else {
          const sheetId = response.data.replies[0].addSheet.properties.sheetId;
          console.log("Created empty sheet: " + sheetId);
          resolve(sheetId);
        }
      }
    );
  });
}

Обратите внимание, что вместо того, чтобы жестко прописывать ссылку на электронную таблицу, мы используем ранее созданную переменную среды SPREADSHEET_ID .

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

Функция batchUpdate в API Google Sheets — это распространенный способ взаимодействия с документами, описание которого приведено здесь .

8. Чтение данных из CSV-файла хранилища.

Теперь, когда у нас есть место для сохранения данных, давайте продолжим разработку нашей облачной функции во встроенном редакторе и воспользуемся API Cloud Storage, чтобы получить фактические данные из только что загруженного файла и сохранить их в строке:

function readCSVContent(sheetsAPI, file, sheetName) {
  return new Promise((resolve, reject) => {
    const storage = new Storage();
    let fileContents = new Buffer('');
    storage.bucket(file.bucket).file(file.name).createReadStream()
    .on('error', function(err) {
      reject('The Storage API returned an error: ' + err);
    })
    .on('data', function(chunk) {
      fileContents = Buffer.concat([fileContents, chunk]);
    })  
    .on('end', function() {
      let content = fileContents.toString('utf8');
      console.log("CSV content read as string : " + content );
      resolve(content);
    });
  });
}

9. Заполните созданный лист.

Теперь пришло время заполнить созданную нами таблицу, используя тот же клиентский API Sheet и только что собранные данные. Воспользуемся этой возможностью, чтобы также добавить немного стиля к столбцам таблицы (изменим размер шрифта верхней строки и сделаем её жирной):

function populateAndStyle(sheetsAPI, theData, sheetId) {
  return new Promise((resolve, reject) => {
    // Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
    // Populate the sheet referenced by its ID with the data received (a CSV string)
    // Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 0,
                columnIndex: 0
              },
              data: theData,
              delimiter: ","
            }
          },
          {
            repeatCell: {
              range: {
                sheetId: sheetId,
                startRowIndex: 0,
                endRowIndex: 1
              },
              cell: {
                userEnteredFormat: {
                  textFormat: {
                    fontSize: 11,
                    bold: true
                  }
                }
              },
              fields: "userEnteredFormat(textFormat)"
            }
          }       
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
      if (err) {
        reject("The Sheets API returned an error: " + err);
      } else {
        console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
        resolve();
      }
    });    
  });
}

Этот код следует добавить в нашу облачную функцию, которая уже готова на 99%!

Обратите внимание, как данные и стили объединяются в несколько requests в один вызов batchUpdate API Google Sheets. Это обеспечивает более эффективное и атомарное обновление.

Обратите внимание, что мы задаем диапазон редактирования, соответствующий размеру созданного нами листа. Это означает, что контент, превышающий 26 столбцов (значение columnCount , использованное при создании листа), будет работать некорректно с этим конкретным кодом.

Если все пойдет хорошо, на этом этапе вы можете:

1. сохранить обновленную функцию
2. перетащите CSV-файл в корзину.
3. Увидите, как соответствующие данные появятся в вашей электронной таблице!

10. Объединение всех элементов и проверка работоспособности системы.

Вызовы только что обсуждавшихся функций можно выполнять в виде последовательных блокирующих вызовов в исходной функции csv2sheet :

  const sheetId = await addEmptySheet(sheetsAPI, sheetName);
  const theData = await readCSVContent(sheetsAPI, data, sheetName);
  await populateAndStyle(sheetsAPI, theData, sheetId);

Если вам нужен полный исходный код функции, он доступен здесь (вероятно, проще получить все в одном комплекте).

После того, как все будет готово, просто загрузите CSV-файл в соответствующий раздел, и ваша электронная таблица обновится, добавив новый лист с содержимым файла. Вот пример CSV-файла, если у вас его нет под рукой.

Попробуйте загрузить в хранилище несколько файлов, чтобы посмотреть, что произойдет!

11. Вот и всё! Пора снести инфраструктуру.

Шутка, никакой инфраструктуры разрушать не нужно, всё было сделано бессерверным способом!

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

12. Что дальше?

На этом завершается данный практический урок, в котором мы шаг за шагом описываем, как отслеживать загрузки в хранилище Cloud Storage в облачной функции для обновления таблицы Google Sheets с помощью соответствующего API.

Вот несколько дальнейших шагов:

• Ознакомьтесь с руководствами по использованию Cloud Functions (включая некоторые рекомендации).
• Пройдите один из обучающих курсов по Cloud Functions.
• Изучите подробнее API Google Таблиц.

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

Мы ценим ваши отзывы!