Using Google Sheets as a backend database

Question

I volunteer for a non-profit organization, and we are currently launching our site through Squarespace. We have hundreds of web pages dedicated to our children's sponsors. Every few months, when we want to update their personal information, we must go to each web page and reprint all the information. We store all our information on a google excel sheet.

My question is: can I associate cells from Google List with specific values ​​on each child web page? Thus, when an Excel worksheet is updated (favorite activity, favorite color, class, age ... etc.), all of this can be updated through the Google Sheet instead of updating the Google Sheet first and then updating each web page. I’ve been studying this for several days now, and I feel that it’s possible, possibly with jQuery, but I am very limited by Squarespace. Thanks!

Answer 1

The short answer is yes.

You can use the Google worksheet as a database for any application, update and manipulate data. I have been doing this for a long time. Google recently published the latest version of the Google V4 API (current) .

I followed this guide: https://developers.google.com/sheets/guides/concepts . In the left pane, you can select the programming language that you want to develop in Quickstart. It supports: PHP. Android Java. Node.js. Python Ruby. Perl Go iOS .NET

The trick is that you need to set an identifier for each entry that relates to the line number. Your column should also be fixed. This is because queries in the Google Sheet API refer to the column number and row number. (Known as the index).

Answer 2

You can check out the following repository that I once used to do the same. https://github.com/souparno/google-sheet-database

Some of the api calls may be old and need updating.

• This is a quick reply, I will update more when I receive some time.

Answer 3

Best of all, you can access your Google spreadsheet using the Google APIs. The problem is that your Google worksheet is publicly available or not. If your worksheet is publicly available, you can use the API without authentication. You can use the Google API visualization with the query language (see links on google) or use the Google Sheets API. But if your personal sheet is confidential, it is mandatory to use OAuth2 authentication through the credentials of the service account. To do this, you need to create a service account (for example, with the owner of the project role). Then you need to download the client_secret.json file, which will be used by you in your code. Finally, you should share your spreadsheet with the email you receive in this file. Look at the file and you will see it. In addition, you should see what you can do from squares. Maybe you can only do this from javascript. You have documents from Google.