Google Sheets integration

Send form responses directly to Google Sheets with automatic file uploads to Google Drive

Forms.md provides seamless integration with Google Sheets, allowing you to capture form responses without setting up a backend server. Responses are saved directly to your spreadsheet, and files are automatically uploaded to Google Drive.

Quick start

1. Specify the sheet name

   In your form template, add the postSheetName setting:

   #! post-sheet-name = Responses
   
2. Deploy the Google Apps Script

   Copy the Apps Script code and deploy it as a web app (instructions below).

3. Set the POST URL

   Add your deployed web app URL to the form:

   #! post-url = https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec
   

Setting up Google Sheets

1. Create a Google Sheet

Create a new Google Sheet with column headers matching your form field names:

name	email	phone	message	_rid

The _rid column is optional but recommended for tracking unique responses and handling partial submissions.

2. Add the Apps Script

Open your Google Sheet and go to Extensions > Apps Script. Delete any existing code and paste the following:

const scriptProp = PropertiesService.getScriptProperties();
scriptProp.setProperty("uploadFolderId", "");
scriptProp.setProperty("recaptchaSecret", "");

function intialSetup() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty("key", activeSpreadsheet.getId());
}

function getSpreadsheetColRef(num) {
  const quotient = Math.floor(num / 26);
  const remainder = num % 26;
  const letter = String.fromCharCode(65 + remainder);
  if (quotient > 0) {
    return getSpreadsheetColRef(quotient - 1) + letter;
  } else {
    return letter;
  }
}

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    // Parse form data fields
    const data = {};
    Object.keys(e.parameter).forEach((key) => {
      data[key] = e.parameter[key];
    });

    // Handle reCAPTCHA
    if (scriptProp.getProperty("recaptchaSecret")) {
      const response = UrlFetchApp.fetch(
        "https://www.google.com/recaptcha/api/siteverify",
        {
          method: "post",
          payload: {
            secret: scriptProp.getProperty("recaptchaSecret"),
            response: data._captcha,
          },
        },
      );
      const responseJSON = JSON.parse(response.getContentText());
      if (!responseJSON.success) {
        throw new Error("CAPTCHA verification failed.");
      }
    }

    // Handle file uploads
    if (e.parameter._fileFields) {
      const fileFields = e.parameter._fileFields.split(",");
      fileFields.forEach((field) => {
        const base64Data = data[field].replace(/^data:.*,/, "");
        const blob = Utilities.newBlob(
          Utilities.base64Decode(base64Data),
          data[`${field}Type`],
          data[`${field}Filename`],
        );
        const folder = DriveApp.getFolderById(
          scriptProp.getProperty("uploadFolderId") ||
            DriveApp.getRootFolder().getId(),
        );
        const uploadedFile = folder.createFile(blob);
        uploadedFile.setSharing(
          DriveApp.Access.PRIVATE,
          DriveApp.Permission.EDIT,
        );
        data[field] = uploadedFile.getUrl();
      });
    }

    // Get the sheet using the name
    const doc = SpreadsheetApp.openById(scriptProp.getProperty("key"));
    const sheet = doc.getSheetByName(data._sheetName) || doc.getSheets()[0];

    // Set up the column references
    const colRefs = {};
    const firstRow = sheet
      .getRange(1, 1, 1, sheet.getLastColumn())
      .getValues()[0];
    for (let i = 0; i < firstRow.length; i++) {
      const colName = firstRow[i];
      colRefs[colName] = i + 1;
    }

    // Get the row number to insert
    let rowToInsert = sheet.getLastRow() + 1;
    const _ridCol = colRefs._rid || false;
    if (_ridCol) {
      const _ridColLetter = getSpreadsheetColRef(_ridCol - 1);
      const _ridValues = sheet
        .getRange(`${_ridColLetter}:${_ridColLetter}`)
        .getValues();
      for (let i = 0; i < _ridValues.length; i++) {
        if (data._rid === String(_ridValues[i])) {
          rowToInsert = i + 1;
        }
      }
    }

    // Insert data
    for (let [key, value] of Object.entries(data)) {
      const colRef = colRefs[key] || false;
      if (colRef) {
        if (typeof value === "string") {
          value = value.trim();
          if (value.startsWith("=")) {
            value = `[${value}]`;
          }
        }
        sheet.getRange(rowToInsert, colRef).setValue(value);
      }
    }

    // Return success
    lock.releaseLock();
    return ContentService.createTextOutput(
      JSON.stringify({ ok: true }),
    ).setMimeType(ContentService.MimeType.JSON);
  } catch (e) {
    lock.releaseLock();
    throw e;
  }
}

3. Run initial setup

1. Save the script (Ctrl+S or Cmd+S)
2. Select the intialSetup function from the dropdown
3. Click Run
4. Authorize the script when prompted

You must run intialSetup once to connect the script to your spreadsheet.

4. Deploy as web app

1. Click Deploy

   Click the Deploy button and select New deployment

2. Configure deployment

   • Type: Select Web app
   • Execute as: Me
   • Who has access: Anyone
3. Copy the URL

   After deployment, copy the web app URL. It should look like:

   https://script.google.com/macros/s/ABC123.../exec
   

Form configuration

Basic setup

Add these settings to your Markdown template:

#! post-url = https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec
#! post-sheet-name = Responses

Multiple sheets

Save different form types to different sheets:

Contact form

#! post-sheet-name = ContactSubmissions

If post-sheet-name is not specified, responses are saved to the first sheet in the workbook.

File uploads

Files are automatically uploaded to Google Drive when using the Google Sheets integration.

Configuring file storage

Set the upload folder ID in your Apps Script:

const scriptProp = PropertiesService.getScriptProperties();
scriptProp.setProperty("uploadFolderId", "YOUR_FOLDER_ID");

1. Create a folder

   Create a folder in Google Drive for file uploads

2. Get the folder ID

   Open the folder and copy the ID from the URL:

   https://drive.google.com/drive/folders/FOLDER_ID_HERE
   
3. Add to script properties

   Update the script with your folder ID:

   scriptProp.setProperty("uploadFolderId", "FOLDER_ID_HERE");
   

If no folder ID is set, files are uploaded to your Google Drive root folder.

File input in forms

const composer = new Composer({
  postUrl: "https://script.google.com/macros/s/.../exec",
  postSheetName: "Applications"
});

composer.fileInput("resume", {
  question: "Upload your resume",
  required: true,
  sizeLimit: 5
});

composer.fileInput("portfolio", {
  question: "Portfolio PDF",
  sizeLimit: 10
});

How it works

1. User selects a file
2. File is converted to base64
3. Sent to Google Apps Script
4. Script uploads file to Google Drive
5. Drive URL is saved in spreadsheet

Files must be sent as base64. Enable this in your form options with sendFilesAsBase64: true.

const form = new Formsmd(template, container, {
  sendFilesAsBase64: true
});

Partial submissions

Track and update partial responses using the _rid (response ID) field.

Setup

1. Add an _rid column to your spreadsheet header row
2. Forms.md automatically generates a unique ID for each response
3. When a user returns to the form, their existing responses are updated

#! post-sheet-name = Survey
#! save-state = true

The _rid value persists in localStorage and updates the same row in Google Sheets when the user submits more data.

reCAPTCHA protection

Protect your forms from spam with Google reCAPTCHA v3.

Setup reCAPTCHA

1. Get reCAPTCHA keys

   Register your site at google.com/recaptcha

2. Add secret to Apps Script

   scriptProp.setProperty("recaptchaSecret", "YOUR_SECRET_KEY");
   
3. Configure form

   const form = new Formsmd(template, container, {
     recaptcha: {
       siteKey: "YOUR_SITE_KEY",
       action: "submit",
       badgePosition: "bottomright"
     }
   });
   

Options

Option	Type	Default	Description
siteKey	string	-	Your reCAPTCHA site key
action	string	"submit"	Action name for tracking
badgePosition	string	"bottomleft"	Position of reCAPTCHA badge
hideBadge	boolean	false	Hide the reCAPTCHA badge

If you hide the reCAPTCHA badge, you must include a notice about reCAPTCHA in your privacy policy.

Multi-step forms

Save data at intermediate steps using the post parameter on slides.

#! post-url = https://script.google.com/macros/s/.../exec
#! post-sheet-name = LongSurvey

# Personal Information

name* = TextInput(
  | question = What is your name?
)

email* = EmailInput(
  | question = What is your email?
)

---
>> post

# Additional Details

phone = TelInput(
  | question = What is your phone number?
)

The >> post directive saves form data when the user navigates to the next slide.

Spreadsheet structure

Recommended columns

Column	Purpose	Required
Field names	Match your form field names exactly	Yes
_rid	Response ID for tracking partial submissions	Recommended
_timestamp	Submission timestamp	Optional
_sheetName	Sheet name (automatically added)	Optional
_captcha	reCAPTCHA token	Optional

Example spreadsheet

name	email	message	_rid	_timestamp
John Doe	[e-mail]	Hello	abc-123	2024-01-15 10:30:00
Jane Smith	[e-mail]	Question	def-456	2024-01-15 11:45:00

Troubleshooting

Responses not appearing

1. Check that column headers in your sheet match form field names exactly
2. Verify the web app URL is correct
3. Ensure the script has been deployed with “Anyone” access
4. Check that intialSetup was run successfully

File uploads failing

1. Verify sendFilesAsBase64: true is set in form options
2. Check the uploadFolderId in script properties
3. Ensure the folder has proper permissions
4. Check file size doesn’t exceed Google Drive limits (10MB default)

Permission errors

1. Make sure you ran intialSetup function
2. Check script execution permissions
3. Redeploy the web app if permissions were changed
4. Verify the sheet ID in script properties

reCAPTCHA verification failing

1. Verify site key and secret key are correct
2. Check that the secret is set in script properties
3. Ensure your domain is registered with reCAPTCHA
4. Test with a different browser or incognito mode

Complete example

#! title = Job Application
#! post-url = https://script.google.com/macros/s/ABC123.../exec
#! post-sheet-name = Applications
#! save-state = true

# Personal Information

name* = TextInput(
  | question = Full Name
)

email* = EmailInput(
  | question = Email Address
)

phone = TelInput(
  | question = Phone Number
  | country = US
)

---
>> post

# Professional Experience

resume* = FileInput(
  | question = Upload your resume
  | sizelimit = 5
)

experience = TextInput(
  | question = Years of experience
  | multiline
)

---

# Thank You

Thank you for applying! We'll review your application and get back to you soon.

Next steps

Form inputs

Learn about all available input types

CLI tool

Generate static sites from your forms