How to safely disconnect & reconnect Google Sheets to Squarespace
⚡⚡ All Squarespace users must reconnect Google Sheets by 23 Sep 2024 ⚡⚡
Learn the right way to do this so you don’t end up with missing data or chaos in your Google Drive
If you manage a Squarespace website that has any type of form or a newsletter block that is sending data to a connected Google Sheet, you should have received an email from Squarespace instructing you to reconnect your forms before 23 September 2024. I’ll walk you through the best order to do things so you don’t miss anything.
The first and most important thing to know is that EVERYONE who is using a Google Sheet connection on any part of their website MUST do this or else the connection will stop working and no data will be passed from their website to their Google Sheets. This change is required due to an update in the way Google Drive manages connection permissions, and the aim is to provide a more secure connection moving forward.
The second thing to note is that this impacts all Google Sheet connections, whether it’s a form block on a web page or blog post, a newsletter signup, a form on a Squarespace 7.0 Cover Page, or a form/newsletter signup on a promotional popup. You may have forms that you have forgotten about, so that’s why I recommend doing things in the order listed below.
WARNING: Before you do anything, it is CRITICAL to know that whenever you perform the disconnection, this will disconnect ALL sheets on ALL websites that you manage. So if you are a designer who has connected sheets for your clients in the past, every single one of these will break when you disconnect. Even if you only manage one website, ALL of the sheets will be disconnected across the ENTIRE WEBSITE. So it's really important that you set aside time to handle all of the reconnections that you need to make immediately after you disconnect.
Step 1: Get a copy of my checklist spreadsheet & prepare colleagues
To help you make note of all the forms on your website, which pages they are on, and the name and location of your attached Google Sheets, I have made a handy checklist that you can use. You can access my spreadsheet template here - be sure to make a copy for yourself or download it so you can fill it in (go to the FILE menu on the template spreadsheet).
You can delete the first column if you are only managing one Squarespace website, and you can delete the second column if you are 100% certain that only one person has ever connected sheets to your website. The disconnecting process must be done by the same person who originally connected the form to the Google Sheet, so if your website was designed/built by someone else, they may be the person in question. (See my note about this at the end!)
You can always see the owner of a Google Sheet by looking for an avatar/icon/name in the top right, just before the share button. Normally the person who is the owner of a sheet is the person who connected the sheet to the form.
At this point you should also inform anyone who is impacted by this change - that is, anyone who uses the Google Sheet(s) - that the change is happening, and you should make sure that they know when you are planning to make the change so they are not using the sheet(s) at the point of changeover.
Step 2: Find all the Google Sheets connected to Squarespace Forms
If you regularly access the Google Sheet(s) that are connected to your website, you probably already know where to find the sheet(s), but if you want a quick tip to find all Google Sheets in your account that are connected to Squarespace, you can go to your Google Sheets home and type “Submitted On” into the search field. That’s because every Squarespace form sheet will have Submitted On as the first column. The search results may show other things, but I guarantee your Squarespace form sheets will show up in your search results.
The reason I suggest doing this as your first step is so you don’t miss any sheets that are connected, even those that you might have connected purely to use as a data backup, or old ones that aren’t currently being used, but might get used again - such as a seasonal promotional popup. Hopefully the person who connected the sheet to the form gave the sheet a sensible name, so you should be able to use that or a quick check of the fields in the sheet to determine which form, newsletter block or popup form on your website that sheet is connected to.
Now that you have all of the sheets, you can fill out the relevant column on my checklist, and it should give you some clues on where to find the form it’s linked to, so you can fill in the other relevant column (the page where the form is).
Step 3: Check the pages & popups on your website
If you’re confident that you found all the connected Google Sheets in Step 2, go to the relevant place(s) where you think the form or newsletter block(s) is/are, and double-check that the name showing on the block matches the name of the Google Sheet you expected to see there.
How to check a form or newsletter block in a page:
In the Pages menu, navigate to the page, then click EDIT
Click the form block and the Pencil/Edit icon
Go to Storage > Additional Storage > Google Drive
The name will be showing under Spreadsheet Name. Your check is done.
How to check a form or newsletter block in a Promotional Popup:
Go to the Marketing menu, then Marketing Tools > Promotional Popup
Click Action
Under Storage, you will see if you have additional storage option for Google Drive
The name will be showing under Spreadsheet Name. Your check is done.
However, if other people have been involved in setting up or administering your website, and you are concerned that there may be other sheets that you personally don’t have access to, then I recommend you open your website’s admin panel and click through to every page in your Pages menu, even the Not Linked pages. Some people may also have forms or newsletter signup blocks within container pages such as blog posts. Remember, you are only concerned with forms that are connected to a Google Sheet, not every single form on the whole site. Perform the steps shown in the first few bullets above to check each form to ensure it’s not connected to a Google Sheet.
Step 4: Disconnect your Google Account from Squarespace
Only now are you ready to do the disconnect/reconnect process. Make sure you have time in your diary to do the disconnect and the reconnection all in one go. Delaying the second part could mean that some data doesn’t get stored in your Google Sheet(s). Squarespace has a step-by-step video guide that you can find here to walk you through the steps needed if you prefer to watch.
Log into your Google Drive account.
Click the gear/cog icon in the top-right corner and select Settings.
Click Manage Apps.
Scroll down til you find Squarespace, and click Options.
Select Disconnect from Drive from the dropdown menu. This will disconnect Google Drive from all forms using that Google Drive account. This is a one-time thing so be sure you are ready!
Note: If you set up the forms on your website a long time ago and were only using the Google Sheet(s) as a backup to email notifications, then you may not even want to reconnect again. That's because as of 2023, all Squarespace forms also store the data into the Squarespace system, and you can easily see all form submissions using the instructions in the Final Step shown at the very bottom of this blog post.
Step 5: Reconnect the form/newsletter blocks to a NEW Google Sheet
You should start with your most important / most frequently used forms. It’s important to note that it is NOT possible to reconnect to an existing sheet. You have to create a brand new sheet during the connection process. This is the most annoying part of the whole deal. My top tip here is to use a name that is different from the old sheet; that’s because it is possible to have multiple sheets with the same name in Google Sheets, and you will not easily be able to tell which is which in your list of sheets. Super confusing chaos!
Go to your form, click Edit and then go to Storage.
Under Additional Storage, click Google Drive and then click Disconnect.
A popup will appear; click Disconnect again to confirm.
Now you will see Google Drive and next to that you can click CONNECT.
Follow the prompts to enable the reconnection. Once you are back in the main form editor, you will need to type the new name for the new spreadsheet. Make sure you use a descriptive and easy to understand name that is different from the old sheet’s name.
Click Back at the top and the little loading icon will show that the connection is happening.
Click Save or Exit at the top of the page to finish the job.
WARNING: If you had any macros, formulas or custom scripts on your old sheet, those will need to be set up again on the new one.
Step 6: Perform a test entry or three
Now you should open the page with the form in a new browser window (or in Preview mode) and complete the form. Go to your Google Drive and find the newly created sheet and check that the form is sending data to the new sheet.
OPTIONAL: Copy/paste old data into the new sheet
If it’s important/convenient to have all the old data and new data in one place, you can copy/paste all the rows from the old sheet into the new one. Once you have done that, I also recommend renaming the old sheet something like XXXXX Archive Up To Sept 2024 so it’s super clear that the old sheet isn’t getting any new updates.
Bear in mind that copy/pasting any formulas or macros that you had set on the old sheet may not work on the new sheet if they are based on row numbers. Be sure to test everything after you copy/paste.
OPTIONAL: Share the new sheet(s)
If other people are involved in using the sheet(s), then you will need to reinvite them to access the new one. As double insurance that they will not use the old sheet(s), you can also remove their access from the old sheet if you wish… although changing the old sheet’s name to contain “Archive” should also help in case they need/want to maintain access to the old one (for example, if you chose not to copy/paste the old data into the new sheet).
Final Step: Do another test on 23 & 24 September, too
You may want to test your form again on 23 September, if you are super paranoid (like me!). It’s unlikely that anything will break, but if you have anxiety about such things, then it certainly won’t hurt to do another test or two on 23 and 24 September, just to alleviate your stress. Remember that you can always check whether the data on your Google Sheet matches the data captured on the Squarespace system, because all form data is stored in the Contacts section of Squarespace. To check the entries submitted on a form follow these steps:
In the main Squarespace menu, go to Contacts, then click the box that says All Contacts, then choose Form Submitters.
On the following screen, click where it says Filter By Form Name and then choose the form you want to check.
The resulting screen will show all form submissions with the most recent at the top.
Hope this helps you get through this annoying - hopefully infrequent! - Google Sheets update.
A Final Note About Designer-Owned Sheets: If your web designer was the owner of your sheet, you might want to consider taking this opportunity to change that. Unless you have confidentiality agreements in place, chances are that your sheet contains personal data that may cause a liability due to GDPR or other privacy laws. In addition, if your designer (rather than you or someone in your business) is the sheet owner, and your relationship with them breaks down, the person goes AWOL or seriously ill, then your data is at risk. For these reasons, I normally recommend to my clients that they are the owners of their own sheets. You can change owners as part of this disconnection process by having your designer complete Step 4 and then YOU carry out Step 5 with your own Google Account.