Investigate how to export form submission to a Google Sheet

[emmambd]

Describe the problem

For the Add a Feed Form , we want to add all submission to a new tab in the pre-existing data updates sheet.

Proposed solution

We need to investigate

• [ ] How to export the form submission to a csv
• [x] How to add that csv as a new tab to a Google sheet

Alternatives you've considered

No response

Additional context

No response

[cka-y]

Findings about the investigation on how to export form submission to Google Sheet

There are multiple ways to achieve this. Here are two methods using TypeScript and Python:

Using TypeScript

The following code demonstrates how to export form submissions to a Google Sheet using TypeScript and Firebase Cloud Functions:

import { initializeApp } from 'firebase-admin/app';
import { onRequest } from 'firebase-functions/v2/https';
import { GoogleSpreadsheet } from 'google-spreadsheet';
import { JWT } from "google-auth-library";

const SCOPES = [
  'https://www.googleapis.com/auth/spreadsheets',
  'https://www.googleapis.com/auth/drive.file',
];

initializeApp(); // for the firebase cloud function

const spreadsheetId = 'sheet_id_from_share_link';

export const writeToSheet = onRequest(
  {
    cors: '*',
    region: 'northamerica-northeast1',
  },
  async (req, res) => {
    try {
      const jwt = new JWT({
        email: "service-account-email@project-id.iam.gserviceaccount.com", // TODO: replace with service account email
        key: "-----BEGIN PRIVATE KEY-----\nPRIVATE KEY\n-----END PRIVATE KEY-----\n", // TODO: replace with private key
        scopes: SCOPES,
      });

      const doc = new GoogleSpreadsheet(spreadsheetId, jwt);

      await doc.loadInfo(); // loads document properties and worksheets
      const newSheetTitle = 'NewSheetTitle';
      const newSheet = await doc.addSheet({ title: newSheetTitle, headerValues: ['Name', 'Age', 'City'] });

      // Write dummy data to the sheet
      const rows = [
        { Name: 'John Doe', Age: '30', City: 'New York' },
        { Name: 'Jane Smith', Age: '25', City: 'Los Angeles' },
      ];

      await newSheet.addRows(rows);

      res.status(200).send('Data written to the new sheet successfully!');
    } catch (error) {
      console.error('Error writing to sheet:', error);
      res.status(500).send('An error occurred while writing to the sheet.');
    }
  }
);

This approach is advantageous because it can be used directly in UI code or as a Firebase Cloud Function, similar to how user information is stored in Firestore. To manage secrets and configuration if using a cloud function, consider setting Firebase secrets as described here. To set secrets in Firebase using environment variables, assuming the SERVICE_ACCOUNT_EMAIL and PRIVATE_KEY environment variables are already set, you can use the following commands:

echo $SERVICE_ACCOUNT_EMAIL | firebase functions:secrets:set SERVICE_ACCOUNT_EMAIL --data-file=-
echo $PRIVATE_KEY | firebase functions:secrets:set PRIVATE_KEY --data-file=-

The deployment process is straightforward; just modify the functions/firebase.json file with information about your new function to add it to the deployment process.

Using Python

The following code demonstrates how to export form submissions to a Google Sheet using Python:

First, install the required packages:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Then, modify the subsequent code to achieve the same functionality as the TypeScript example:

import os
from google.auth.transport.requests import Request
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

SCOPES = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive.file"]
SERVICE_ACCOUNT_FILE = "path/to/service/account/credentials.json"  # TODO: replace with the path to your service account credentials file

spreadsheet_id = "sheet_id_from_share_link"

def write_to_sheet():
    creds = None
    creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)

    try:
        service = build("sheets", "v4", credentials=creds)
        sheet = service.spreadsheets()

        # Create a new sheet (tab)
        requests = [{
            'addSheet': {
                'properties': {
                    'title': 'NewSheetTitle',
                    'gridProperties': {
                        'rowCount': 100,
                        'columnCount': 20
                    }
                }
            }
        }]

        body = {
            'requests': requests
        }

        response = sheet.batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
        new_sheet_id = response['replies'][0]['addSheet']['properties']['sheetId']

        # Write dummy data to the sheet
        values = [
            ['Name', 'Age', 'City'],
            ['John Doe', '30', 'New York'],
            ['Jane Smith', '25', 'Los Angeles']
        ]

        body = {
            'values': values
        }

        result = sheet.values().update(
            spreadsheetId=spreadsheet_id,
            range='NewSheetTitle!A1',
            valueInputOption='RAW',
            body=body
        ).execute()

        print(f'{result.get("updatedCells")} cells updated.')

    except HttpError as err:
        print(err)

write_to_sheet()

This code can also be run as a cloud function. For deployment, modify the infra/functions-python/main.tf file to include the necessary infrastructure configurations. Secrets can be directly configured through this setup.

Exporting to CSV

To achieve adding the submitted information from the form to the Google Sheet, it is not required to export to CSV, as demonstrated in the above examples.

[emmambd]

Should be considered in next steps after #517

[davidgamez]

Pending question: (cc: @emmambd )

• Do we need to add a new tab every time a feed is added, or a new row in a "reserved" tab for feeds added via web?
• Do we need to notify our team via email about the addition of a new feed?

Next step:

• Create implementation issue

[emmambd]

@davidgamez

1: Reserved tab

2: Yes! There's already a Github bot for this that Fred implemented, so perhaps we can re-purpose it for this

[emmambd]

Implementation issue made in #553