Read data from Google spreadsheet in Angular

Mehul Kothari

Dec 12, 2020·4 min read

Github: https://github.com/mehulk05/MyShop

Demo Link: https://mehulk05.github.io/MyShop/#/

If you’ve ever found yourself asking ‘how can I access Google Sheet spreadsheets with JavaScript?’, then you’re probably not alone. You only have to type something like ‘reading google sheets data using javascript’ into Google and you’ll be inundated with a bevy of solutions to this conundrum.

The problem is, in my experience, they’re either very complex and overblown, or quite difficult to get up and running with (e.g. poor documentation), or they’re just a bit too prescriptive with how they serve you those precious results. So that's why I am taking this opportunity to write a simple solution

Firstly, is this right for my needs?

The following blog allows for reading only from a publicly published Google Sheet. If your needs look like these, then it might be a great fit:

• You are able to publish your Google Sheet publicly
• You have a relatively simple data set in a single sheet (multiple sheets is a planned feature)
• You only need to read the data
• You don’t need access to more advanced functionality (such as caching or OAuth) provided by the official Google Sheets API.
• You want a simple, straightforward means to get data > do things with data > celebrate!

Step 1: Create Google sheet and save

• First, we are going to the google spreadsheet and create a new sheet. Once you will create a sheet you will get one unique id in the URL for a particular sheet. Copy that id somewhere which we are going to need in our code. Check the below Image for understanding

Step 2: Publish the spreadsheet

1. Click File > Publish to the web
2. Select the ‘Link’ tab
3. Choose either ‘Entire Document’ or (more likely) a specific sheet name
4. In the next drop-down, choose either ‘Web page’ or ‘CSV’. It doesn’t really matter which
5. Expand the section at the bottom
6. Check the box next to ‘Automatically republish when changes are made’
7. Click ‘Publish’.

Step 3: Get sheet number

• There can be instances where you can have multiple sheets in one file. Check the below image for understanding.

• So to get the sheet number for which sheet we want the data go to the URL https://spreadsheets.google.com/feeds/worksheets/<your sheet id > /private/full. The sheet id here is we go in step 2 when we created a new sheet.
• A JSON data will appear which I showed below in the image. Copy the sheet number generated for you which I marked in yellow as shown in the below image.

Step 4 Create an Angular Project

So I am just quickly skipping a few steps here assuming you know how to create an angular project and create service and components.

Sheet service file

public getCooker(): Observable<any> {const sheetno="o6isq5z"
     const sheetid = "1GLoPM2OKSGQPypZeBL3uCl4diAi4YXLye-LrXIx4jr4"
     const url = 
`https://spreadsheets.google.com/feeds/list/${sheetid}/${sheetno}/public/values?alt=json`;
    
        return this.http.get(url)
          .pipe(
            map((res: any) => {
              const data = res.feed.entry;
    
              const returnArray: Array<any> = [];
              if (data && data.length > 0) {
                data.forEach(entry => {
                  const obj = {};
                  for (const x in entry) {
                    if (x.includes('gsx$') && entry[x].$t) {
                      obj[x.split('$')[1]] = entry[x]['$t'];
                    }
                  }
                  returnArray.push(obj);
                });
              }
              return returnArray;
            })
          );
      }

Sheet.Component.ts

constructor(private csv: CsvParserService,private csvFilter:GetfilterFromCSVService) { }ngOnInit(): void {this.csv.getCooker().subscribe(res => {
      this.productData = res
      this.productDataFilter = res
      console.log(this.productData)
    })
}

So you will get output in chrome console-like as shown in the below image.

Conclusion

• This is a simple and cleaner solution to read data from one spreadsheet file which can have multiple sheets.
• So this is a quick workaround solution where you don't want to configure a database for the small requirements.
• A good cleaner way where you want to load data dynamically which is frequently changing (for ex: E-commerce website, Stock items of your small business shop)
• Cost-effective (no storage cost), faster access (google database are faster)

Limitations:

• We can only read data from spreadsheets. Need to write a separate function to modify or delete items.
• The file needs to be published with public access (not good for sensitive data).

Demo Images of my App using this approach (Ecommerce website)

Also read

Authentication Using JWT in MEAN Stack

Authentication using JWT in a mean stack app from scratch

itnext.io

How to Add Medium Blogs on your React App

How to embed Medium blogs on your React app and other websites

medium.com

COVOID 19 Tracker (Statistics) app Using Angular

Live Demo: https://mehulk05.github.io/COVOID-19/

codeburst.io

Medium Like Blogging App Using Angular 9 and Firebase

The Medium Like Blogging App Complete Demo

codeburst.io