Use Post Requests To Send Form Data To A Google Sheet

Why Google Sheets Is an Ideal Form Database Solution

Google Sheets provides an excellent solution for storing form data, especially for smaller projects or when you need a quick setup. Unlike traditional databases that might require complex configuration, Google Sheets offers several advantages:

• Zero infrastructure costs - The free tier is very generous

• Familiar spreadsheet interface that most team members can navigate

• Built-in collaboration tools for sharing and editing data

• Easy export options to CSV, Excel, or other formats

• Automatic cloud backup of all your submissions

• Simple filtering and sorting capabilities

While not suitable for high-traffic applications processing thousands of submissions daily, Google Sheets works perfectly for most small to medium websites collecting contact information, feedback, or other form data.

What You’ll Build in This Tutorial

In this tutorial, we’ll create a complete system that:

1. Securely authenticates with Google’s API
2. Sends form data from an AstroJS website to a Google Sheet
3. Validates form inputs before submission
4. Handles success and error states appropriately

Step-by-Step Guide: Setting Up Your Google Environment

1. Creating Your Google Sheet

1. Open Google Sheets and create a new sheet. Name the sheet as well as the sheet tab at the bottom of the sheet.

For best results, add column headers that match the data you’ll be collecting. For example, if you’re creating a contact form, your headers might be “Timestamp”, “Name”, “Email”, “Phone”, and “Message”.

2. Setting Up Google Cloud Project

2. Go to Google Cloud Console

3. Click on “Select a project”

4. Click on “New Project”

5. Name the project and click “Create”

Choose a descriptive name related to your website or form purpose. This helps you identify the project later if you have multiple Google Cloud projects.

6. Open the new project with the same “Select a project” button

Creating a Service Account for Secure API Access

A service account acts as a bot that will interact with your Google Sheet on behalf of your website:

7. Click on “IAM & Admin”

8. Click on “Service Accounts”

9. Click on “Create Service Account”

10. Name the service account and click “Create”

Give your service account a descriptive name like “form-submission-bot” so you can easily identify its purpose.

11. Click on “Manage keys”

12. Click on “Add key”, select “JSON” and click “Create”. This will save the JSON file to your computer with your credentials.

This JSON file contains sensitive information that grants access to your Google account’s resources. Never commit this file to a public repository or share it publicly.

Enabling the Google Sheets API for Your Project

13. Go back to the Google Cloud Console and click on “APIs and Services”. In the search bar type “Google Sheets API”

14. Click on “Google Sheets API”

15. Click “Enable”

This allows your service account to interact with Google Sheets through the API.

Sharing Your Sheet with the Service Account

16. Open the Google Sheet and click “File” -> “Share” -> “Share with others”. Add the form bot as an editor. The email for the form bot is on the console page and in the credentials JSON file.

This step is crucial - without proper sharing permissions, your service account won’t be able to write data to the sheet.

Implementing the Code in Your AstroJS Project

1. Setting Up Environment Variables

17. Create new environment variables using the credentials from the JSON file, and add the id from the sheet url: https://docs.google.com/spreadsheets/d/<id>

1
PUBLIC_GOOGLE_SHEET_ID=
2
PRIVATE_GOOGLE_SHEET_CREDENTIALS_PROJECT_ID=
3
PRIVATE_GOOGLE_SHEET_CREDENTIALS_PRIVATE_KEY_ID=
4
PRIVATE_GOOGLE_SHEET_CREDENTIALS_PRIVATE_KEY=
5
PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_EMAIL=
6
PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_ID=
7
PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_X509_CERT_URL=

2. Installing Required Dependencies

18. Install the web-auth-library

1
npm install web-auth-library

This library simplifies the OAuth authentication process with Google’s APIs.

Creating the Google Sheets Integration Service

19. Create a new file src/lib/server/google-sheets.ts

20. Add the following code to the file. This is a reusable function if you want to append data to different sheet tabs within the same sheet project. The names are case sensitive. It will append the data to the first empty row of the sheet. If there’s no empty rows, it will create a new row at the end of the sheet.

1
import { getAccessToken } from 'web-auth-library/google';
2

3
export async function appendToSheet({ sheetName, data }: { sheetName: string; data: string[] }) {
4
  try {
5
    const accessToken = await getAccessToken({
6
      credentials: JSON.stringify({
7
        type: 'service_account',
8
        project_id: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_PROJECT_ID,
9
        private_key_id: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_PRIVATE_KEY_ID,
10
        private_key: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_PRIVATE_KEY.replace(/\\n/g, '\n'),
11
        client_id: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_ID,
12
        client_email: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_EMAIL,
13
        auth_uri: 'https://accounts.google.com/o/oauth2/auth',
14
        token_uri: 'https://oauth2.googleapis.com/token',
15
        auth_provider_x509_cert_url: 'https://www.googleapis.com/oauth2/v1/certs',
16
        client_x509_cert_url: import.meta.env.PRIVATE_GOOGLE_SHEET_CREDENTIALS_CLIENT_X509_CERT_URL,
17
      }),
18
      scope: 'https://www.googleapis.com/auth/spreadsheets',
19
    });
20

21
    const result = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${import.meta.env.PUBLIC_GOOGLE_SHEET_ID}/values/${sheetName}!A1:append?valueInputOption=RAW`, {
22
      method: 'POST',
23
      body: JSON.stringify({
24
        range: `${sheetName}!A1`,
25
        majorDimension: 'ROWS',
26
        values: [data],
27
      }),
28
      headers: { Authorization: `Bearer ${accessToken}` },
29
    });
30

31
    return result;
32
  } catch (error) {
33
    return error as Error;
34
  }
35
}

This function handles:

• Authentication with Google’s API using your service account credentials
• Formatting the data for insertion into your sheet
• Making the API request to append the data
• Error handling for any issues that might occur

Building a Type-Safe Form Handler with Astro Actions

21. Create an Astro Action that calls the appendToSheet function. Included here is a zod schema for the form data validation.

1
import { ActionError, defineAction } from 'astro:actions';
2
import { appendToSheet } from '@/lib/server/google-sheets';
3
import { z } from 'zod';
4

5
const contactFormSchema = z.object({
6
  name: z.string(),
7
  email: z.string().email(),
8
  phone: z.string().optional(),
9
  message: z.string(),
10
});
11

12
export const server = {
13
  'form/contact': defineAction({
14
    accept: 'form',
15
    input: contactFormSchema,
16
    handler: async ({ name, email, phone, message }) => {
17
      try {
18
        const result = await appendToSheet({
19
          sheetName: 'Contact',
20
          data: [new Date().toUTCString(), name, email, phone || '', message],
21
        });
22

23
        if (result instanceof Error) {
24
          throw result;
25
        }
26

27
        if (result.status !== 200) {
28
          throw new Error(`Response status: ${result.status}: ${result.statusText}`);
29
        }
30

31
        return { success: true };
32
      } catch (error) {
33
        if (error instanceof Error) {
34
          throw new ActionError({
35
            code: 'INTERNAL_SERVER_ERROR',
36
            message: error.message,
37
          });
38
        }
39
      }
40
    },
41
  }),
42
};

Astro Actions provide a clean way to handle form submissions with built-in validation. The Zod schema ensures that all required fields are provided.

Creating a User-Friendly Contact Form

22. Now create the form page. Our starter kit has a contact form already created in src/pages/contact.astro

Your form HTML should include fields that match your schema. For example:

1
<form>
2
  <div class="form-group">
3
    <label for="name">Name</label>
4
    <input type="text" id="name" name="name" required />
5
  </div>
6

7
  <div class="form-group">
8
    <label for="email">Email</label>
9
    <input type="email" id="email" name="email" required />
10
  </div>
11

12
  <div class="form-group">
13
    <label for="phone">Phone (optional)</label>
14
    <input type="tel" id="phone" name="phone" />
15
  </div>
16

17
  <div class="form-group">
18
    <label for="message">Message</label>
19
    <textarea id="message" name="message" required></textarea>
20
  </div>
21

22
  <button type="submit">Send Message</button>
23
</form>

Implementing Client-Side Form Handling

23. Write the script to submit the form data to the Astro Action.

1
<script>
2
import { actions } from 'astro:actions';
3

4
const form = document.querySelector('form');
5
const submitButton = form?.querySelector('button[type="submit"]');
6
const statusMessage = document.createElement('div');
7
statusMessage.className = 'form-status';
8
form?.appendChild(statusMessage);
9

10
form?.addEventListener('submit', async (e) => {
11
  e.preventDefault();
12

13
  // Show loading state
14
  if (submitButton) {
15
    submitButton.disabled = true;
16
    submitButton.textContent = 'Sending...';
17
  }
18

19
  statusMessage.textContent = '';
20

21
  const formData = new FormData(form);
22
  const { error } = await actions['form/contact'](formData);
23

24
  if (error) {
25
    // Show error message to user
26
    statusMessage.textContent = `Error: ${error.message}`;
27
    statusMessage.className = 'form-status error';
28
  } else {
29
    // Show success message to user
30
    statusMessage.textContent = 'Thank you! Your message has been sent.';
31
    statusMessage.className = 'form-status success';
32
    form.reset();
33
  }
34

35
  // Reset button state
36
  if (submitButton) {
37
    submitButton.disabled = false;
38
    submitButton.textContent = 'Send Message';
39
  }
40
});
41
</script>

This script:

• Prevents the default form submission
• Shows a loading state while the form is being processed
• Handles both success and error states
• Resets the form on successful submission

Testing and Deploying Your Google Sheets Integration

23. Run the AstroJS server in development mode and submit the form.

1
npm run dev

24. Open the Google Sheet and see the form data. You should see your form submission appear in the sheet with all fields properly populated.

Deploying to Production

25. Add your new environment variables to your hosting provider.

Most hosting platforms like Vercel, Netlify, or Cloudflare Pages have a section in their dashboard for environment variables. Make sure to add all the variables from your .env file.

Security Best Practices for Google Sheets Integration

• Never expose your private credentials in client-side code
• Use server-side validation to prevent malicious data
• Consider implementing rate limiting to prevent form spam
• Regularly rotate your service account keys

Extending Your Google Sheets Form Solution

• Add email notifications when new form submissions arrive
• Create different sheets for different types of forms
• Implement custom formatting or data processing before storage
• Add analytics tracking for form submissions
• Build an admin dashboard to view recent submissions

Google Sheets provides a flexible, accessible way to store form data without the complexity of a traditional database. For most small to medium websites, this solution offers the perfect balance of simplicity and functionality.