Import Data from Google Sheets to Firestore using Google Apps Script

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

Congratulations!

You have learned how to import Google sheets data to Firestore using Google Apps Script.

Happy Learning :)