Skip to content

Instantly share code, notes, and snippets.

@scriptburn scriptburn/.gcloudignore forked from sangramrath/index.js
Last active Jul 31, 2019

Embed
What would you like to do?
Node.js 8 Cloud Function to write to a Sheets document
# This file specifies files that are *not* uploaded to Google Cloud Platform
# using gcloud. It follows the same syntax as .gitignore, with the addition of
# "#!include" directives (which insert the entries of the given .gitignore-style
# file at that point).
#
# For more information, run:
# $ gcloud topic gcloudignore
#
.gcloudignore
# If you would like to upload your .git directory, .gitignore file or files
# from your .gitignore file, remove the corresponding line
# below:
.git
.gitignore
node_modules
*.csv
*.csv
node_modules/
// 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 file = { name: req.body.name, bucket: req.body.bucket }
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 = file.name.slice(0,-4);
var getError = false;
const sheetId = await addEmptySheet(sheetsAPI, sheetName).catch(err => {
getError = err
});
if (getError && getError.indexOf('already exists') == -1) {
throw (getError)
} else {
await clearSheet(sheetsAPI, sheetName)
}
const theData = await readCSVContent(sheetsAPI, file, sheetName);
await populateSheet(sheetsAPI, theData, sheetName); // wrap into "await Promise.all()" if making calls returning multiple promises
res.status(200).send("done");
};
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();
}
});
});
}
function clearSheet(sheetsAPI, sheetName) {
return new Promise((resolve, reject) => {
const editRange = sheetName + "!A1:Z";
const initTrixHeadersRequest = {
spreadsheetId: process.env.SPREADSHEET_ID,
range: editRange,
}
sheetsAPI.spreadsheets.values.clear(initTrixHeadersRequest, function(err, response) {
if (err) {
reject("The Sheets API returned an error: " + err);
} else {
console.log(sheetName + " cleared");
resolve();
}
});
});
}
{
"name": "sheets",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"start": "functions-framework --target=function2sheet --port 8001"
},
"repository": {
"type": "git",
"url": "git+https://gist.github.com/3d6f2ed8459bf858276259df25a6d746.git"
},
"author": "",
"license": "ISC",
"bugs": {
"url": "https://gist.github.com/3d6f2ed8459bf858276259df25a6d746"
},
"homepage": "https://gist.github.com/3d6f2ed8459bf858276259df25a6d746",
"dependencies": {
"@google-cloud/functions-framework": "^1.2.1",
"@google-cloud/storage": "^3.0.4",
"googleapis": "^41.0.1"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.