Skip to main content
The Google Sheets integration automatically adds each form submission as a new row in a Google Sheets spreadsheet. This provides an easy way to collect, analyze, and share submission data.

​
Features

  • Automatic Sync: Each submission is instantly added as a new row
  • Column Mapping: Form fields are automatically mapped to spreadsheet columns
  • Header Management: Column headers are automatically created and updated
  • Hidden Fields: Option to include hidden fields in the spreadsheet
  • File Support: File upload fields include signed URLs to access uploaded files

​
Prerequisites

  • A Google account
  • Permission to create/edit spreadsheets in Google Drive

​
Setup

1

Add Google Sheets Integration

In your form editor:
  1. Go to Integrations tab
  2. Select Google Sheets
  3. Click Connect Google Account
2

Authorize OpnForm

  1. Sign in to your Google account
  2. Grant OpnForm permission to access Google Sheets
  3. You’ll be redirected back to OpnForm
3

Create or Select Spreadsheet

Choose one of the following:Option A: Create New Spreadsheet
  • Click Create New Spreadsheet
  • A new spreadsheet with your form name will be created
  • Column headers will be automatically added
Option B: Use Existing Spreadsheet
  • Click Select Existing Spreadsheet
  • Choose a spreadsheet from your Google Drive
  • Select the worksheet tab to use
4

Configure Column Mapping

  • OpnForm automatically maps form fields to spreadsheet columns
  • You can reorder or remove columns as needed
  • Column headers are created from field names
5

Test Integration

Submit a test form to verify data appears in your spreadsheet

​
How It Works

​
Initial Setup

When you create a Google Sheets integration:
  1. OAuth Connection: OpnForm connects to your Google account using OAuth 2.0
  2. Spreadsheet Creation: A new spreadsheet is created (or you select an existing one)
  3. Column Mapping: Form fields are mapped to spreadsheet columns
  4. Headers Set: First row contains field names as headers

​
On Each Submission

When a form is submitted:
  1. Header Sync: Column headers are updated if form fields changed
  2. Data Formatting: Submission data is formatted for spreadsheet insertion
  3. Row Addition: A new row is appended with the submission data
  4. File URLs: File uploads are converted to signed URLs

​
Column Mapping

Form fields are automatically mapped to spreadsheet columns:
Form Field TypeSpreadsheet Value
Text, Email, PhoneString value
NumberNumeric value
DateDate string
Select (dropdown)Selected option
Multi-selectComma-separated values
File UploadSigned URL to file
Checkbox”Yes” or “No”
Hidden FieldString value (if enabled)

​
Hidden Fields

By default, hidden fields are included in the spreadsheet. You can configure this in the integration settings.

​
Implementation Details

​
OAuth Integration

The Google Sheets integration requires OAuth authentication to access your Google account. From api/app/Integrations/Handlers/GoogleSheetsIntegration.php:31-34: 
public static function isOAuthRequired(): bool
{
    return true;
}

​
Spreadsheet Manager

The SpreadsheetManager class handles all interactions with Google Sheets API. From api/app/Integrations/Google/Sheets/SpreadsheetManager.php:49-65: 
public function create(Form $form): Spreadsheet
{
    $body = new Spreadsheet([
        'properties' => [
            'title' => $form->title
        ]
    ]);

    $spreadsheet = $this->driver->spreadsheets->create($body);

    $this->data->url = $spreadsheet->spreadsheetUrl;
    $this->data->spreadsheet_id = $spreadsheet->spreadsheetId;
    $this->data->columns = [];

    $this->updateHeaders($spreadsheet->spreadsheetId);

    return $spreadsheet;
}

​
Submission Handler

When a submission occurs, data is formatted and appended to the spreadsheet. From api/app/Integrations/Google/Sheets/SpreadsheetManager.php:145-156: 
public function submit(array $submissionData): static
{
    $this->updateHeaders($this->data->spreadsheet_id);

    $row = $this->buildRow($submissionData);

    $this->addRow(
        $this->data->spreadsheet_id,
        $row
    );

    return $this;
}

​
Building Rows

Submission data is formatted to match the column order: From api/app/Integrations/Google/Sheets/SpreadsheetManager.php:130-143: 
public function buildRow(array $submissionData): array
{
    $formatter = (new FormSubmissionFormatter($this->integration->form, $submissionData))
        ->useSignedUrlForFiles()
        ->showHiddenFields()
        ->outputStringsOnly();

    $fields = $formatter->getFieldsWithValue();

    return collect($this->data->columns)
        ->map(function (array $column) use ($fields) {
            $field = Arr::first($fields, fn ($field) => $field['id'] === $column['id']);
            return $field ? $field['value'] : '';
        })
        ->toArray();
}

​
Token Refresh

Google OAuth tokens expire after a certain time. OpnForm automatically refreshes tokens when needed. From api/app/Integrations/Google/Google.php:37-59: 
public function refreshToken(): static
{
    $provider = $this->formIntegration->provider;
    if (!$provider?->refresh_token) {
        return $this;
    }

    $this->client->refreshToken($provider->refresh_token);

    $token = $this->client->getAccessToken();
    if (!$token || !isset($token['access_token'])) {
        return $this;
    }

    $updateData = ['access_token' => $token['access_token']];

    if (isset($token['refresh_token'])) {
        $updateData['refresh_token'] = $token['refresh_token'];
    }

    $provider->update($updateData);

    return $this;
}

​
Spreadsheet Structure

​
Example Spreadsheet

NameEmailPhoneMessagePrioritySubmitted At
John Doe[email protected]555-0123Looking for infoHigh2024-03-05 12:34:56
Jane Smith[email protected]555-0456Need supportMedium2024-03-05 13:45:12

​
Column Headers

Headers are automatically created from form field names and updated when form fields change: 
public function updateHeaders(string $id): static
{
    $columns = $this->buildColumns();

    $headers = array_map(
        fn ($column) => $column['name'],
        $columns
    );

    return $this->setHeaders($id, $headers);
}

​
Conditional Logic

You can configure the Google Sheets integration to only add rows when certain conditions are met:
  • Only add to spreadsheet when “Status” is “Approved”
  • Filter based on priority level
  • Exclude test submissions
Configure conditions in the integration settings.

​
Troubleshooting

Check OAuth connection: Re-authorize Google account if neededVerify spreadsheet access: Ensure the spreadsheet hasn’t been deletedCheck conditional logic: Ensure conditions are met for rows to be addedReview integration logs: Check Form Settings → Integrations → Google Sheets → View Events
Update column mapping: Edit the integration and rebuild columnsCheck for custom blocks: Custom blocks (starting with nf-) are automatically skippedRefresh headers: The integration automatically updates headers on each submission
Signed URLs expire: File URLs are signed and expire after a certain timeDownload files: Download files immediately if long-term access is neededCheck permissions: Ensure files are properly stored and accessible
Automatic refresh: Tokens are automatically refreshed by OpnFormRe-authorize: If refresh fails, remove and re-add the Google accountCheck refresh token: Ensure the OAuth provider has a valid refresh token stored
This can happen if:
  • Integration is added multiple times to the same form
  • Form is submitted multiple times
  • Multiple integrations point to the same spreadsheet
Check your integration settings and remove duplicates.

​
Limitations

Google Sheets API Limits: Google has rate limits and quotas for API usage:
  • 100 requests per 100 seconds per user
  • 500 requests per 100 seconds per project
High-volume forms may hit these limits. Consider using webhooks for high-volume scenarios.

​
Best Practices

Use Dedicated Spreadsheets

Create a separate spreadsheet for each form instead of using multiple sheets in one file. This improves performance and organization.

Regular Backups

Periodically download your spreadsheets as backups. While data is stored in Google Drive, having local copies is recommended.

Clean Field Names

Use clear, concise field names in your form as they become column headers:
  • ✅ “Customer Email”
  • ✅ “Phone Number”
  • ❌ “email_field_123”

Limit Hidden Fields

Only include hidden fields if necessary. They add extra columns that may clutter your spreadsheet.

​
Privacy Considerations

Data Storage: Submissions are stored in your Google Drive, which is subject to Google’s privacy policies and terms of service.File URLs: Signed URLs for file uploads may expire. Download important files for long-term storage.Sharing: Be careful when sharing spreadsheets. They may contain sensitive submission data.

​
Advanced: Manual Spreadsheet Setup

If you prefer to manually set up your spreadsheet:
  1. Create a spreadsheet in Google Sheets
  2. Add column headers in the first row matching your form field names
  3. Share the spreadsheet with OpnForm’s service account
  4. Note the spreadsheet ID from the URL
  5. Contact support to manually configure the integration

Build docs developers (and LLMs) love

Get started for freeTalk to us