# Submit a Form to Google Sheets

I needed a way to collect all submissions of a form for Docbrew into a Google Spreadsheets. If you also need to do this, here how to do that.

# Create a Google Sheet

You can use an existing sheets or a new one.

Put the following headers into the first row:

You can add other headers if you need to gather more information.

# Create a Google Apps Script

• In your Google spreadsheets tab, in the navbar, click on Tools > Script Editor… which should open a new tab.
• Now, delete the function myFunction() {} block within the Code.gs tab.
• Paste the following script in it's place and File > Save:

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

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

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

sheetName is the name of your sheets so make sure that it is the same name if you renamed it.

# Run the setup function

• Next, go to Run > Run Function > initialSetup to run this function.
• In the Authorization Required dialog, click on Review Permissions.
• Sign in or pick the Google account associated with this project.
• You should see a dialog that says Hi {Your Name}, Submit Form to Google Sheets wants to...
• Click Allow

# 4. Add a new project trigger

• On your project, click on Edit > Current project’s triggers. It should open a new tab.

• Click on Create a new trigger, a dialog should appear.

• In the dropdowns select doPost
• Set the events fields to From spreadsheet and On form submit
• Then click Save, you would have to sign in and pick the Google account associated with this project.

# 5. Publish the project as a web app

• Back to the script page, click on Publish > Deploy as web app…. A dialog should appear.

• Project Version is set to New and nothing need to be changed.
• Leave Execute the app as: set to Me(your@address.com).
• For Who has access to the app: select Anyone, even anonymous.
• Click Deploy.
• In the popup, copy the Current web app URL from the dialog.
• And click OK.

IMPORTANT! If you have a custom domain with Gmail, you might need to click OK, refresh the page, and then go to Publish > Deploy as web app… again to get the proper web app URL. It should look something like https://script.google.com/a/yourdomain.com/macros/s/XXXX….

# Submit form data

In order to work, you need to do a POST query to the URL you copied in the step before. Your data need to be submitted in a form data with the name of property corresponding to the header in your spreadsheets.

It was quite long to setup your form, but now it's easy to reproduce with this blueprint!

Until next time!