Skip to content

Instantly share code, notes, and snippets.

@abhishek0196
Last active July 5, 2022 20:39
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save abhishek0196/15cc7d79952edbabc205ef7b0e68d529 to your computer and use it in GitHub Desktop.
Copying data from Firebase Firestore to a Google Sheet in real time via Cloud Functions

Copying data from Firebase Firestore to a Google Sheet in real time via Cloud Functions If you are trying this,then you might have come across to another post from CodingDoug in which he had done the same using Firebase Realtime Database.

Here is the link https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07 to his work , if you want with Realtime Database.

For the setup ,you can refer to the link. https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07#setup

The firestore structure for the database is :- http://bit.ly/2GmJczm

This script , appends a row to the existing sheet when ever there is a new document created under the collection "Customer1" (You change it according to your requirements)

Here in the file index.ts in line number:31 has the sheet name: new (Again you can update it according to your requirement) Moreover from line 36-38 (obj["name"], obj["net-amount"], obj["mobile"]) are the fields I was using (You can update it).


Thats It . Enjoy.

import * as functions from 'firebase-functions'
import * as _ from 'lodash'
import { google } from 'googleapis'
const sheets = google.sheets('v4')
import * as admin from 'firebase-admin';
admin.initializeApp();
const spreadsheetId = '' // Your Sheets ID
const serviceAccount = require('../serviceAccount.json') //Location to the serviceAccount You downloaded from firebase.
const jwtClient = new google.auth.JWT({
email: serviceAccount.client_email,
key: serviceAccount.private_key,
scopes: [ 'https://www.googleapis.com/auth/spreadsheets' ], // read and write sheets
})
const jwtAuthPromise = jwtClient.authorize()
exports.newCustomer = functions.firestore
.document('/customer1/{docID}')
.onCreate(async (snap:any) => {
if(snap.exists)
{
const obj = JSON.parse(JSON.stringify(snap.data()));
await jwtAuthPromise
sheets.spreadsheets.values.append({
auth: jwtClient,
spreadsheetId:spreadsheetId,
range:'<sheet_name>!A2', //here the new is the individual sheet name and A2 is the starting range.
valueInputOption:'RAW',
requestBody: {
"values": [
[
obj["name"],
obj["net-amount"],
obj["mobile"]
]
]
}
}, (err, result:any) => {
if (err) {
// Handle error
console.log(err);
} else {
console.log('%d cells updated.', result.updatedCells);
}
});
}
});
@simoubouss
Copy link

thank you ..it's Works for me

@abhishek0196
Copy link
Author

thank you ..it's Works for me

Glad to hear.

@Stoffy92
Copy link

Stoffy92 commented Feb 20, 2020

I get Error: Error occurred while parsing your function triggers.

\functions\index.js:44
.onCreate(async (snap: any) => {

SyntaxError: missing ) after argument list
^^^^

Any ideas how to resolve this?

@abhishek0196
Copy link
Author

I get Error: Error occurred while parsing your function triggers.

\functions\index.js:44
.onCreate(async (snap: any) => {

SyntaxError: missing ) after argument list
^^^^

Any ideas how to resolve this?

Seeing the error it seems that the braces might be missing, please have a check on it.
It would be great if you can share the code snippet. So that I can help you further.

@Stoffy92
Copy link

Codepen is here: https://codepen.io/Stoffy92/pen/PoqGXRL

I don't think I'm missing anything...I think it has to do with eslint

@abhishek0196
Copy link
Author

abhishek0196 commented Feb 21, 2020

I tried the same code, it was successfully deployed. Check with tslint.

@JaeHwanWO
Copy link

I'm getting error Unable to parse range: new!A2

@abhishek0196
Copy link
Author

I'm getting error Unable to parse range: new!A2

You can define your own range in the range: key.
Any ways the new you see in the range is the individual sheet name.
I hope this resolves your issue.

@JaeHwanWO
Copy link

JaeHwanWO commented Feb 28, 2020

Do you have any clue about how to set 'timestamp' type? It keeps giving me error about milliseconds and seconds, but I can't figure out how to solve them.

@abhishek0196
Copy link
Author

Do you have any clue about how to set 'timestamp' type? It keeps giving me error about milliseconds and seconds, but I can't figure out how to solve them.

Can you please explain your doubt, and also some scenarios, it will be easy for me to help you.

@panjireza
Copy link

i cant afford with theese, i dont even know how it start. its too hard to understand for me as a newbie.
is there any full tutorial step by step?

@abhishek0196
Copy link
Author

i cant afford with theese, i dont even know how it start. its too hard to understand for me as a newbie.
is there any full tutorial step by step?

Sure I will try to modify for better understanding thanks for the feedback.

@abhishek0196
Copy link
Author

I will be uploading a video soon with all the steps.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment