Import Data from Google Sheets to Firestore using Google Apps Script
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.
3.Creating a new Firebase project
Open Firebase console and click add a project like below
Firebase console: Add project
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
1VUSl4b1r1eoNcRWotZM3e87ygkxvXltOgyDZhixqncz9lQ3MjfT1iKFw
and then click Add
Firestore Library
Select the latest version and click Save
Firestore-Version
Now the Firestore library has been added.
7. Storing data in Firestore
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
function covidFunc() {
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 spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "covid";
var sheet = ss.getSheetByName(sheetname);
// get the last row and column in order to define range
var sheetLR = sheet.getLastRow(); // get the last row
var sheetLC = sheet.getLastColumn(); // get the last column
var dataSR = 2; // the first row of data
// define the data range
var sourceRange = sheet.getRange(2,1,sheetLR-dataSR+1,sheetLC);
// get the data
var sourceData = sourceRange.getValues();
// get the number of length of the object in order to establish a loop value
var 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 rows
for (var i=0;i<sourceLen;i++){
if(sourceData[i][1] !== '') {
var data = {};
var dateSt = sourceData[i][0].toString();
var stDate = new Date(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
Reference Links
Firestore Library — Click here to view
Google Sheet — Click here to view
Congratulations!
You have learned how to import Google sheets data to Firestore using Google Apps Script.
Happy Learning :)
