# Google Sheets integration

{% 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: Querying This Documentation

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

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

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

The question should be specific, self-contained, and written in natural language.
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.