Skip to content

Instantly share code, notes, and snippets.

@sangramrath
Forked from alexismp/index.js
Last active May 8, 2020 14:44
Show Gist options
  • Save sangramrath/1dc4a0fe70366eb6116d2d52d7ac6e1b to your computer and use it in GitHub Desktop.
Save sangramrath/1dc4a0fe70366eb6116d2d52d7ac6e1b to your computer and use it in GitHub Desktop.
Node.js 8 Cloud Function to write to a Sheets document
// Copyright 2018 Google LLC.
// SPDX-License-Identifier: Apache-2.0
const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage");
exports.function2sheet = async (req, res) => {
var fileName = req.name;
const auth = await google.auth.getClient({
scopes: ['https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/devstorage.read_only']
});
const sheetsAPI = google.sheets({version: 'v4', auth});
const sheetName = fileName.slice(0,-4);
const sheetId = await addEmptySheet(sheetsAPI, sheetName);
const the
= await readCSVContent(sheetsAPI, req, sheetName);
await populateSheet(sheetsAPI, theData, sheetName); // wrap into "await Promise.all()" if making calls returning multiple promises
};
function readCSVContent(sheetsAPI, file, sheetName) {
return new Promise((resolve, reject) => {
const storage = new Storage();
let fileContents = new Buffer('');
let rows = [];
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');
let lines = content.split(/\r\n|\r|\n/);
lines.forEach(function(line) {
rows.push ( line.split(',') );
});
console.log("CSV content read (" + rows.length + " rows) from " + file.name);
resolve(rows);
});
});
}
function addEmptySheet(sheetsAPI, sheetName) {
return new Promise((resolve, reject) => {
const addEmptySheetParams = {
spreadsheetId: process.env.SPREADSHEET_ID,
resource: {
requests: [{
addSheet: {
properties: {
title: sheetName,
index: 1,
gridProperties: {
rowCount: 2000,
columnCount: 26,
frozenRowCount: 1
}
}
}
}]}
};
sheetsAPI.spreadsheets.batchUpdate(addEmptySheetParams, 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);
}
});
});
}
function populateSheet(sheetsAPI, theData, sheetName) {
return new Promise((resolve, reject) => {
const editRange = sheetName+"!A1:Z";
const initTrixHeadersRequest = {
spreadsheetId: process.env.SPREADSHEET_ID,
range: editRange,
resource : {
range: editRange,
majorDimension: "ROWS",
values: theData
},
valueInputOption: "RAW"
}
sheetsAPI.spreadsheets.values.update(initTrixHeadersRequest, function(err, response) {
if (err) {
reject ("The Sheets API returned an error: " + err);
} else {
console.log(sheetName + " sheet populated with " + theData.length + " rows" );
resolve();
}
});
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment