> For the complete documentation index, see [llms.txt](https://docs.forms.md/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.forms.md/integrations/google-sheets-integration.md).

# Google Sheets integration

Save form submissions directly in Google Sheets.

{% embed url="<https://youtu.be/vFTiRejsKG4>" %}
Google Sheets integration
{% endembed %}

{% hint style="info" %}
Please watch the video above to learn how to get started with the Google Sheets integration.
{% endhint %}

## Apps script code

The following is the script for the integration:

```javascript
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
    // If the sheet name is not provided, get the first sheet of the document
    const doc = SpreadsheetApp.openById(scriptProp.getProperty("key"));
    const sheet = doc.getSheetByName(data._sheetName) || doc.getSheets()[0];

    // Set up the column references
    // This contains the column numbers for the headers (first row)
    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 the request data
    // By default, this is the last row
    // If the incoming request has an "_rid" that matches an existing row,
    // then that row is used for the 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
    // Make sure to remove all formulae (starts with "=")
    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 ok
    lock.releaseLock();
    return ContentService.createTextOutput(
      JSON.stringify({ ok: true }),
    ).setMimeType(ContentService.MimeType.JSON);
  } catch (e) {
    // Throw error
    lock.releaseLock();
    throw e;
  }
}
```

## Spam protection with Google reCAPTCHA

To use Google reCAPTCHA with the apps script, add your site's reCAPTCHA secret key in this line:

```javascript
scriptProp.setProperty("recaptchaSecret", "<YOUR_SECRET_KEY>");
```

After that, save and deploy again to add spam protection.

## File uploads

For file uploads, set the `sendFilesAsBase64` [option](/getting-started/options.md) to `true` during instantiation:

```javascript
const formsmd = new Formsmd(
  composer.template,
  document.getElementById("my-form-container"),
  {
    sendFilesAsBase64: true
  }
);
```

## Save in a different sheet

Set the `postSheetName` [form setting](/getting-started/settings.md) to the name of the sheet where you want to save the submissions:

```javascript
const composer = new Composer({
  postSheetName: "sheet4"
});
```

Generates the following Markdown-like syntax:

```
#! post-sheet-name = sheet4
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.forms.md/integrations/google-sheets-integration.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.