Develop a HTTP API in AWS EC2 For Sending Data to Google Sheets

Introduction:

In today's data-driven world, integrating external services and APIs into your applications is essential for enhancing productivity and automating tasks. One such integration that can streamline data management is connecting an HTTP API hosted on AWS EC2 to Google Sheets. This article will guide you through the process of creating a basic HTTP API on an Amazon EC2 instance that enables you to send and store data in Google Sheets.

Why AWS EC2?

Amazon Elastic Compute Cloud (EC2) is a widely used cloud computing service that provides scalable and flexible virtual server instances. EC2 allows developers to deploy applications on virtual machines with various configurations, making it an ideal choice for hosting HTTP APIs.

Why Google Sheets?

Google Sheets is a powerful and accessible spreadsheet application that can serve as a simple and convenient database for a variety of applications. By sending data to Google Sheets, you can easily organize, analyze, and share information with collaborators in real time.

Key Steps:

Set Up an AWS EC2 Instance:
- Create an AWS account if you don't have one.
- Launch an EC2 instance based on your requirements (e.g., operating system, instance type, etc.). Amazon Linux 2 can be used.
- Configure security groups and key pairs to ensure secure access to your EC2 instance. Open the port for accessing it through the web.
Install Necessary Software:
- Connect to your EC2 instance using SSH.
- Install Node.js and npm (Node Package Manager) to run your HTTP API. There might be a problem in installing the latest version, whenever creating something in VM it's always recommended to use LTS versions. NVM can be used to choose between different node versions.
- Set up other required dependencies, such as Express.js, body-parser, and the google-spreadsheet library for integrating with Google Sheets.
  
  https://theoephraim.github.io/node-google-spreadsheet/#/
```
  npm install express body-parser google-spreadsheet google-auth-library
```

Develop the HTTP API:

Create an Express.js application to define routes for sending data to Google Sheets.
Implement POST requests for receiving data from client applications.

  const { GoogleSpreadsheet } = require('google-spreadsheet');
  const { JWT } =require('google-auth-library');

  const serviceAccountAuth = new JWT({
      email: {email}
      key:{private key}
      scopes: [
      'https://www.googleapis.com/auth/spreadsheets',
      ],
  });

  const doc = new GoogleSpreadsheet('google-sheet-id');
  const express = require('express');
  const bodyParser = require('body-parser');
  const { writeToGoogleSheet } = require('./google-sheets');
  const app = express();
  const port = process.env.PORT || 3000;

  app.use(express.json());
  app.post('/save-data', async (req, res) => {
    try {
      const data =req.body.data;
      await writeToGoogleSheet(data,doc);
      res.status(200).json({ message: 'Data saved successfully' });
    } catch (error) {
      console.error('Error:', error);
      res.status(500).json({ message: 'Internal server error' });
    }
  });
  app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
  });

Set Up Google Sheets Integration:
- Create a Google Sheets document that you want to use for storing data.
- Generate service account credentials in the Google Developer Console and share the Google Sheets document with the associated service account email address.

Develop Google Sheets Integration Logic:

Write JavaScript code to use the google-spreadsheet library to interact with your Google Sheets document.
Implement functions for authenticating the service account, loading the Google Sheets document, and writing data to the spreadsheet.

  const { GoogleSpreadsheet } = require('google-spreadsheet');
  const doc = new GoogleSpreadsheet('google-sheet-id');
  async function writeToGoogleSheet(data,doc) {
  await doc.loadInfo();
  console.log(doc.title);
  let sheet=doc.sheetsByTitle["Sheet1"]
  await sheet.addRows([
    { data: data }
  ]);
    console.log('Data added to Google Sheet');
  }
  module.exports = {
    writeToGoogleSheet,
  };

Configure CORS:
- To enable your HTTP API to accept requests from external sources, configure Cross-Origin Resource Sharing (CORS) settings on your server.
Test and Deploy:
- Test your HTTP API endpoints using tools like Postman and create a basic HTML form for sending data to your API.
- Deploy your HTTP API on your EC2 instance so it's accessible over the internet.

Conclusion:

In this article, we'll walk through each of these key steps in detail to create a fully functional HTTP API hosted on an AWS EC2 instance that can seamlessly send and store data in Google Sheets. By the end, you'll have a working integration that can be extended and customized to suit your specific needs, whether it's for automating data collection, creating real-time dashboards, or any other application that requires data storage and retrieval. Stay tuned for our step-by-step guide on creating this powerful integration!