You can automatically insert data collected by your Shack Forms Pro form into a Google Sheets spreadsheet. This is a five-step process:
- Sign up for a Google Mail account
- Sign up for a Google Cloud Platform account
- Create your Google OAuth Client app
- Authorize your Shack Forms Pro component to connect to your Google Sheets spreadsheets
- Connect a selected Shack Forms Pro form module with a selected Google Sheets spreadsheet.
Let's take a look at this process in more detail.
Step #1. Sign up for Google Mail
- Go to https://www.google.com/gmail/about/ and click the blue "Create an account" button:
- Follow the onscreen instructions.
IMPORTANT: Don't log out of your Google Mail account once finished.
Step #2. Sign up for a Google Cloud Platform account
- Go to https://cloud.google.com and click the blue "Sign in" button:
- Follow the onscreen instructions.
IMPORTANT! Stay logged in in your Google Cloud Platform account once you've finished creating it.
Step #3. Create your OAuth Client app
- In your Google Cloud Platform, in the top left corner, click the hamburger icon:
- Click "IAM & Admin" and click "Create a Project":
- Follow onscreen instructions to create a Google Cloud Platform project.
- Once you've finished, you should see your project dashboard:
Excellent! You are ready to start creating your Google OAuth Client app.
- Go to "APIs & Services" > "Credentials":
- You will see the "Credentials" area. Click "CREATE CREDENTIALS":
- You will see a drop-down list of options. Click "OAuth client ID":
- Click "CONFIGURE CONSENT SCREEN":
- Click "External" and click "CREATE":
- On the next screen, fill in the mandatory "App name", "User support email", and "Developer contact information" fields and click "SAVE AND CONTINUE":
- You will see the Step 2. Scopes screen:
- At the bottom of the screen, click "SAVE AND CONTINUE" button:
- You will be presented with the "Step 3. Test users" area.
- Click "ADD USERS"
- You will see the "Add users" area, as shown in the next image. Enter your email address and click "ADD" twice:
- Click "SAVE AND CONTINUE":
- This will bring you to the "Summary" screen. Click "Library":
- You will see the Google API Library. Scroll down to the "Google Workspace" group of libraries and click "Google Drive API":
- On the next screen, click "Enable":
- After a few seconds of screen activity, you will see the "Google Drive API" page. Click "Library":
- Go again to the "Google Workspace" section and click "Google Sheets API":
- Click "Enable":
- Click "CREATE CREDENTIALS":
- You will see the "Create credentials" screen. Click "User data" and click "Done":
- Click "Credentials":
- Click "CREATE CREDENTIALS":
- Click "OAuth client ID" dropdown:
- Click inside the "Application type" box, and in the dropdown list of options, click "Web application":
- In your Joomla admin dashboard, go to Components > Shack Forms Pro > Messages and in the top right corner click the "Options" button:
- You will see the "Shack Forms Pro Configuration" screen. Click the "Google Sheets" tab, click on the redirect URL to copy it:
- Go back to your Google Cloud Console dashboard. Click the "ADD URI" button in the "Authorized redirect URIs" section:
- Paste the redirect URL you copied a minute ago and click "CREATE":
- You will see the "OAuth client created" box:
Good job! You created your Google OAuth Client app.
You are ready to move on and authorize your Shack Forms Pro form module to connect to your Google Sheets spreadsheets via the Google OAuth Client app.
Step #4. Authorize your Shack Forms Pro component to connect to your Google Sheets spreadsheets via the Google OAuth Client app
- Click on the icon next to the Client ID number to copy it.
IMPORTANT: don't yet close the "OAuth client created" box.
- In your Joomla dashboard, go to your "Shack Forms Pro Configuration" screen and paste your OAuth Client ID number in the "Client ID" box:
- Go to your Google Cloud Console, and in the "OAuth client created" box, copy the "Client secret":
- In your Joomla dashboard, go to the "Shack Forms Pro Configuration" screen, paste the Client secret in the "Secret" box and click "Save":
- The "No access" and "Grant access" buttons appear. Click "Grant access":
- You will see the "Sign in with Google" box. Click on your Google Mail address, which is linked to your Google Cloud Console:
- Allow and agree to everything the next few boxes will prompt you to allow and agree to.
- You will see that your "Sign-in with Google" box is completely blank. That's normal.
- Go to the "Shack Forms Pro Configuration" screen and click "Save".
- You will see that the "Google API Access Code" parameter now displays the green "Has access" button. You will also notice that besides the Redirect URL, the box underneath displays some more information:
Wonderful! At this point, you authorized your Shack Forms Pro to connect to your Google Sheets spreadsheets.
Now is a good time to connect a Shack Forms Pro form module of your choice to your chosen Google Sheets spreadsheet.
Step 5. Connect a Shack Forms Pro form module with a Google Sheets spreadsheet
- Go to Components > Shack Forms Pro > Modules. Open up your form module for editing.
- Go to the "Integrations" tab and set the "Insert form data into a spreadsheet" parameter to "Yes":
- You will see the "Select" drop-down appearing in the "Document ID" field:
- Click on "Select", click on the title of the Google Sheet spreadsheet to which you wish to pass data submitted by this Shack Forms Pro form and then click on the blue "Get columns names" button:
- You will see the column's names appear and are highlighted. Copy them and click Save:
- In your Google Sheets, open up the spreadsheet you selected in the previous step and paste the copied column names in the first row:
Sent on;Ticket;Page title;Page URL;Attachments;Name;Email;Phone;Message;Email;IP Address;Browser;User Agent;Screen resolution
- Click in the A1 cell. You will see the A1 cell selected:
- Go to Data and click the "Split text to columns":
- You will see the "Separator" box. Click on "Detect automatically" and then on "Semicolon":
- You will see the columns are now neatly separated:
Good job! Your spreadsheet is ready to take in data submitted using your Shack Forms Pro form module.
Now would be a good time to give it a test.
Here is our test form with the data being submitted:
- Click "Send" to submit your form data.
- Go to your Google Sheets spreadsheet. You will see that you submitted data populated your spreadsheet, similar to the one in the next image:
