How to connect the SiteManager Form to Google Sheets.

This article will explain how to connect the SiteManager form to a Google Sheet. Every time the form will be submitted, a new row will be added to your Google Sheet.

Connecting to Google Sheets

To use the Google Sheets action, it is required for you to generate an authentication key. Without this key, pushing changes to the sheet won't be possible.

All steps to obtain a key are written out below, and are demonstrated in this video.

console

Obtaining an authentication key

You can obtain an authentication key in your Google Cloud console. If you've never used this console before, you might get a little overwhelmed with all the options and items in the console. Don't worry though, we'll guide you through it step by step!

Create a project

You first need to create a project. You can compare this project to a project in the SiteManager platform. For each SiteManager project that you want to connect sheets to, it's best to create a new project in the Google console. With Google's awesome analytics tools, it will be possible to analyze each project in depth. However, if you don't want to do this, it is also possible to create 1 general project and use 1 or multiple keys in this project.

You can create a new project in the taskbar at the top. Choose to select a project, or click on the name of the current project. A popup window will open where you can see all your current projects. You can create a new project by clicking the button in the top right corner.

Sheets console new project

You need to fill in a project name and a project location. For clarity, it is advised to use the same project name as in the SiteManager platform. The location is the organization this project belongs to. If you have an extensive Google Workspace setup, you will be able to select your own company or subdivision. If not, just select the no organization option, as this doesn't change anything to the inner working of the service.

Add the Sheets API

Now that your project is up and running, it is time to create the API that will do all the hard work. Go to APIs & Services in the menu on the left, or by click on 'Go to APIs overview' on the API tile in the dashboard. You can enable APIs and Services at the top of the API dashboard. Next, you will want to find the Google Sheets API and enable this API in this project.

Create credentials

Now that the API is enabled, you will need to create some credentials for it. Click create credentials and step through the wizard. It is important that you select the settings as shown in the example below.

Sheets console wizard step 1

  1. Which API are you using: Select the Google Sheets API.
  2. Where will you be calling the API from: Although it might sound logically to select web browser in this step, you actually need to select web server. This is because the real connection between your website and the Google Sheet will happen on the server.
  3. What data will you be accessing: Select application data. We will only be accessing data from the sheet, and not from a Google account.
  4. App Engine or Compute Engine: We will not be using these, so select the option where you're not using them.

In the following step, you will need to create a service account. This account will be used to write the data to your sheet. You can see this account as a dummy Google user that will have access to your sheet.

Sheets console wizard step 2

  1. Service account name: The name of the service account. You can just use the project name for this step.
  2. Role: This a very important part, as your service account needs to have edit rights to the sheet. Without these right, it won't be possible to add data to your sheet. Select project and choose editor or owner. Both of these roles have editor access.
  3. Service account ID: The ID of the service account. You can just use the project name for this step. Take a mental note of the email address that is shown, as we will need this in a later step.
  4. Key type: Leave this option on JSON.

When you click on continue, the authentication key will be downloaded. Save this key to a location on your computer, as you won't be able to download this key again!

In the credentials overview, the email address of the service account will be shown. Copy this email address, as we need it in the next step.

Sharing the sheet

You can now leave the Google Cloud console. Head over to your Google Sheet, or create a new one if you don't have one already. You can share your sheet with the green button in the top right corner. Here you can paste the email address of the service account that you have copied in the previous step. Make sure to share the sheet with editor access. You can uncheck the notify checkbox, as our service account isn't a real person.

Sheets share

Sheets toolstack

Google Sheets app

When you install a form, the Google Sheets app (and all other relevant apps) will be automatically installed in your project. You can find the app in the toolstack section of the project.

In this app you will be able to upload the authentication key that you have downloaded from the Google Cloud console. The authentication process should now be completed!

Sheets app

Form action

You can add the Google Sheets action by clicking the 'Edit Form Actions' button. Here you can add all the actions, including the Google Sheets action.

Sheets action

  1. Spreadsheet URL: The URL (or ID) of your spreadsheet. Just copy and paste the URL of the sheet.
  2. Sheet name: The name of the sheet (tab) in the spreadsheet. You can find the name at the bottom of your spreadsheet. This name is case-sensitive, so make sure to copy the exact name.

Build form

You can insert multiple form inputs to collect different kinds of data. Be sure to include a submit button (as that is required for every form). No other fields are required, but without any it will be hard to collect data.

Input data

It isn't necessary to add extra data to your input fields. By default, all the fields will be sent to the sheet. You can however still control the fields manually. You can do so by editing the input data on a field and by adding the Google Sheets data. Once you've added one Google Sheets data to a field, only the fields with this data will be sent to the sheet.