1. Введение
Цель этой лаборатории кода — помочь вам понять, как написать облачную функцию , которая будет реагировать на загрузку CSV-файла в облачное хранилище , читать его содержимое и использовать его для обновления Google Sheet с помощью Sheets API .
Это можно рассматривать как автоматизацию этапа «импорта в формате CSV», который в противном случае выполнялся бы вручную. Это гарантирует, что вы сможете анализировать данные (возможно, созданные другой командой) в электронной таблице, как только они станут доступны.
Вот как выглядит реализация:
2. Настройка и требования
Самостоятельная настройка среды
- Войдите в Cloud Console и создайте новый проект или повторно используйте существующий. (Если у вас еще нет учетной записи Gmail или G Suite, вам необходимо ее создать .)
Запомните идентификатор проекта — уникальное имя для всех проектов Google Cloud (имя, указанное выше, уже занято и не подойдет вам, извините!). Позже в этой лаборатории он будет называться PROJECT_ID .
- Далее вам необходимо включить биллинг в Cloud Console, чтобы использовать ресурсы Google Cloud.
Прохождение этой лаборатории кода не должно стоить много, если вообще стоит. Обязательно следуйте всем инструкциям в разделе «Очистка», в которых рассказывается, как отключить ресурсы, чтобы не взимать плату за пределами этого руководства. Новые пользователи Google Cloud имеют право на участие в программе бесплатной пробной версии стоимостью 300 долларов США .
3. Создайте и настройте Google Sheet и включите API.
Для начала создадим новый документ Таблиц (этот лист может принадлежать любому пользователю). После создания запомните его идентификатор; она будет использоваться как переменная среды для функции, которую мы напишем:
В консоли GCP включите API Google Таблиц в новом проекте, перейдя в раздел «API и службы», а затем в раздел «Библиотека API»:
В разделе «IAM и администратор» перейдите к «Учетные записи служб» и запишите адрес электронной почты учетной записи службы App Engine по умолчанию. Он должен иметь вид your-project-id@appspot.gserviceaccount.com . Конечно, вы также можете создать свою собственную учетную запись службы, посвященную этому действию.
Наконец, просто предоставьте этой учетной записи службы права на редактирование своей таблицы с помощью кнопки «Поделиться»:
Благодаря этой настройке мы теперь можем написать нашу облачную функцию и настроить ее для использования этой учетной записи службы. Он сможет писать в этот документ электронной таблицы, который мы только что создали.
4. Создайте сегмент хранения
Давайте создадим корзину, в которой наша облачная функция будет отслеживать новые файлы CSV.
В консоли используйте меню слева, чтобы перейти к «Хранилище»... :
... и создайте новую корзину под названием csv2sheet-POSTFIX (замените POSTFIX чем-то уникальным) со всеми остальными настройками, установленными на значения по умолчанию:
5. Создайте облачную функцию
Теперь мы можем создать облачную функцию под названием csv2sheet , которая запускается при загрузке файлов в определенную корзину облачного хранилища. Код будет написан на Node.js 8 с асинхронными функциями с использованием встроенного редактора прямо в Cloud Console:
Обязательно установите для триггера значение «Облачное хранилище» и измените имя корзины на то, которое вы создали на предыдущем шаге.
Также обновите точку входа для функции, которую мы собираемся записать в csv2sheet :
Теперь измените тело функции на:
- используйте API Cloud Storage и Sheets
- пометить функцию
csv2sheetкакasync - получите
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 с API Cloud Storage и Google Sheet в качестве двух зависимостей, которые мы будем использовать (используйте вкладку встроенного редактора консоли PACKAGE.JSON):
{
"name": "csv2sheet",
"version": "0.0.42",
"dependencies": {
"googleapis": "^51.0.0",
"@google-cloud/storage": "^5.0.1"
}
}
После того, как вы все настроили, как описано, нажимайте «Создать»! Через несколько минут ваша функция должна быть создана и развернута.
6. Настройка аутентификации и API таблиц.
Прежде чем писать какой-либо дополнительный код в нашей облачной функции с помощью встроенного редактора, нам нужно заблокировать создание Google Client API с соответствующими областями хранения и листа (помните, что это часть 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. Используйте Sheets API, чтобы создать пустой лист.
С помощью клиента Sheets API мы можем создать простой новый лист в нашем документе, но прежде чем идти дальше, вот краткая заметка о словарном запасе:
- электронная таблица является фактическим документом, и на нее ссылается ее идентификатор (обсуждаемый выше и видимый в URL-адресе документа).
- лист — это одна из вкладок в документе, и на него можно ссылаться по его имени (имени вкладки) или идентификатору, сгенерированному при создании листа.
Имея это в виду, вот функция, использующая клиент API Таблиц для создания пустого листа в позиции 2 (обычно после значения по умолчанию «Лист1»), с 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 в Sheets API — распространенный способ взаимодействия с документами, описанный здесь .
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 Sheets API. Это обеспечивает более эффективное и атомарное обновление.
Также обратите внимание, что мы определяем диапазон редактирования, соответствующий размеру созданного нами листа. Это означает, что содержимое, содержащее более 26 столбцов (значение columnCount , использованное при создании листа), не будет работать с этим конкретным кодом.
Если все идет хорошо, на этом этапе вы можете:
- сохранить обновленную функцию
- поместите CSV-файл в корзину
- см. всплывающее окно соответствующих данных в вашей электронной таблице!
10. Собираем все вместе и тестируем поток
Вызовы функций, которые мы только что обсудили, могут быть выполнены как последовательные блокирующие вызовы в исходной функции csv2sheet :
const sheetId = await addEmptySheet(sheetsAPI, sheetName);
const theData = await readCSVContent(sheetsAPI, data, sheetName);
await populateAndStyle(sheetsAPI, theData, sheetId);
Если вам нужен полный исходный код функции, он доступен здесь (вероятно, проще получить все в одном наборе).
Когда все будет готово, просто загрузите CSV-файл в нужную корзину и наблюдайте, как в вашу электронную таблицу добавляется новый лист с содержимым файла. Вот образец CSV-файла, если у вас его нет под рукой.
Попробуйте загрузить в корзину несколько файлов и посмотрите, что произойдет!
11. Вот и все! Пора сносить инфраструктуру
Шучу, нет никакой инфраструктуры, которую можно было бы снести, все это было сделано без сервера!
При желании вы можете удалить облачную функцию и созданную вами корзину или даже весь проект.
12. Что дальше?
На этом завершается эта лабораторная работа, которая описывает шаги по прослушиванию загрузок в сегмент облачного хранилища в облачной функции для обновления Google Sheet с использованием соответствующего API.
Вот некоторые последующие шаги:
- Ознакомьтесь с практическими руководствами по облачным функциям (включая некоторые рекомендации).
- Ознакомьтесь с одним из руководств по облачным функциям.
- Подробнее о API Google Таблиц
Если вы столкнулись с проблемами в этой лаборатории кода, не стесняйтесь сообщать о любой проблеме, используя ссылку в левом нижнем углу.
Ваш отзыв ценен!