✨Crafting Code with a Smile for 8 Years:) Merging the Formal Dance of Angular, the Playful Rhythms of React, and the Next-level Moves of Next.js 🚀
Overview
In this post, you will learn how to take the data from Google Sheets and store those records in Firestore using Google Apps Script
Objectives
What is Apps Script
What is Firestore
Create a new Firebase project
Create a new Google Sheet and enable Apps Script
Generate Private key from GCP console
Integrate Firestore in Apps Script
Storing data in Firestore
Prerequisites
G Suite account
1. What is Apps Script
Google Apps Script is a rapid application development platform that makes it fast and easy to create business applications that integrate with G Suite. You can write code in modern JavaScript and have access to built-in libraries such as Gmail, Calendar, Drive, etc.,
2. What is Firestore
Cloud Firestore is a fast, fully managed, serverless, cloud-native NoSQL document database that simplifies storing, syncing, and querying data for your mobile, web, and IoT apps on a global scale.
Its client libraries provide live synchronization and offline support, while its security features and integrations with Firebase and Google Cloud Platform (GCP) accelerate building truly serverless apps.
Now Create a Project page will be opened. Add the project name and click Continue. Now you will be asked to select the account for Analytics select the account (If you are new then create an Analytics account). Now click Create.
Once the project is created click Database
Database
And then click Create database for Cloud Firestore
Cloud Firestore
Now select Start in Test mode and click Next
Cloud Firestore Rules
Now select the Cloud Firestore location
Firestore Location
Click Done
4. Create a new Google Sheet and enable Apps Script
We will use the COVID-19 data of US in Google Sheet and we will write the script to store those data from Google Sheet to Firestore using Apps Script
In the Google Sheet click Tools -> Script Editor
Google Sheets — Script Editor
Once you click the Script Editor you will see a window like below
Script Editor
Here we will write the logic to take the data from Google Sheet and then store those data into Firestore
5.Generate Private key from GCP console
To get the data from Google sheets we are in need of some private keys from Google Cloud Console (GCP) which we can get it from the API&Services -> Credentials -> Service Accounts.
Open GCP Console and select the project which you have created for creating Firestore
GCP Project Selection
Now select API & Services and Click Credentials, In the Credentials page scroll down to Service accounts
Service Accounts
Now click on the Email which will open a pop-up modal like below
Create Key
Click on CREATE KEY and then make sure the format is JSON and then click CREATE
A private key in JSON Format
Once the CREATE button is clicked a file with JSON format will be downloaded. Just open the file and explore from that file we will be using
CLIENT_EMAIL
PRIVATE_KEY
PROJECT_ID
6. Integrate Firestore in Apps Script
To access Firestore in Apps Script we need to install the Firestore library to do that click Resources -> Libraries and then add the below the number in the input box
Now we will write the logic to get the data from Google Sheet and add those records to COVID19 collection in Firestore
First, we will initialize the keys which we have taken from Step 6 like below
functioncovidFunc() {
const email = "add the client_email here";
const key = "add the private_key here";
const projectId = "add the project id here";
var firestore = FirestoreApp.getFirestore (email, key, projectId);
}
Once the initialization is completed we will write the logic to get the data from Google Sheet like below
// get document data from ther spreadsheetvar ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "covid";
var sheet = ss.getSheetByName(sheetname);
// get the last row and column in order to define rangevar sheetLR = sheet.getLastRow(); // get the last rowvar sheetLC = sheet.getLastColumn(); // get the last columnvar dataSR = 2; // the first row of data// define the data rangevar sourceRange = sheet.getRange(2,1,sheetLR-dataSR+1,sheetLC);
// get the datavar sourceData = sourceRange.getValues();
// get the number of length of the object in order to establish a loop valuevar sourceLen = sourceData.length;
Make sure to give a name for the sheet in your Google Sheet
Now we will create a loop to get data from each row like below
// Loop through the rowsfor (var i=0;i<sourceLen;i++){
if(sourceData[i][1] !== '') {
var data = {};
var dateSt = sourceData[i][0].toString();
var stDate = newDate(dateSt);
var stringfied = JSON.stringify(stDate);
var updatedDt = stringfied.slice(1,11);
data.date = updatedDt;
data.country = sourceData[i][1];
data.state = sourceData[i][2];
data.fips = sourceData[i][3];
data.cases = sourceData[i][4];
data.deadths = sourceData[i][5];
firestore.createDocument("covid",data);
}
}
In the above code, we have taken the date and stringified it and sliced the date because when we try to store the date as it is. In Firestore the date will be stored as an object with date and time.
Now the code would be like below
Now if the run the code you can see the output like below
Source: Nidhinkumar
Storing date as Timestamp
Suppose if your date object is like 3/2/2020 9:06:07 and from that, you need to extract only the date 2020–03–02 and for that date, if you want to get the timestamp 1583087400000 you can try the below solution.
Suppose if u don’t have a date in your google sheet and you want to create it from the app script means you can try this second solution
