Google Sheets - Forms and JSON Operations

Many solutions can be developed for the relationships between Google services via Apps Script. This integration can, of course, also be fed from external sources.

In this article, I will demonstrate two topics. In the first flow, we’ll create a form using Google Forms and access the form’s records in JSON format; in the second section, we’ll transfer an existing JSON file to Google Sheets.

Google Forms and Sheets Integration

Google Forms is an application for form management that integrates seamlessly with Google Docs, Google Sheets, and Google Slides. Through its services, you can easily collect data using various templates such as contact information, event attendance, invitations, product/service order forms, job applications, exams, and course evaluations.

When you select a template and create your first form, a form page will open with a Questions and Answers tab where you can edit the form.

Upon clicking the Answers tab, you’ll be presented with options to select a response destination (new or existing spreadsheet), download responses in CSV format, or print them. Additionally, you can create a new spreadsheet by clicking the Google Sheets icon located immediately to the side.

Once the Sheets integration is established, all responses provided in the form will be recorded in a table. This enables access to relevant fields through other spreadsheets, Looker Studio, and/or third-party applications. For further ideas, please refer to the article titled Forms: Logical Operations and Email Routing.

If you’d like to automate the form creation process, please review the article titled What is Google Apps Script? What is it for?.

The forms created can be made accessible online via links.

https://docs.google.com/forms/d/<form-id>/prefill

Similarly, the Google Sheets spreadsheet associated with the created form can also be shared with users and/or all visitors via a link.

Google Sheets and JSON Access

The entire content of Google Sheets or specific individual tables can be made available on a web page or downloadable in various formats (csv, tsv, pdf, xlsx, ods). To achieve this, follow the File > Publish to the web steps¹.

Timestamp	Time	When it was 23:30 on Saturday, June 12, 2006, in Greenwich, what date and time would it be at a location 25 degrees east longitude?	Email	Task
22.11.2020 14:17:27	-	Saturday, June 12, 2006, at 21:30	test@domain.com	Marketing
22.11.2020 14:17:31	-	Wednesday, June 13, 2006, at 01:10	info@google.com	Design
29.12.2020 00:04:40	-	Saturday, June 11, 2006, at 20:30	hi@websites.com	Software Development

However, it is also possible to access the table content in JSON format with a simple procedure. To do this, follow the File > Share steps.

https://docs.google.com/spreadsheets/d/<sheet-id>/edit?usp=sharing

You simply need to append the unique <sheet-id> value found in the obtained link to the corresponding field in the URL below. The <tab-number> indicates the specific sheet; by default, you can provide the value 1¹.

https://spreadsheets.google.com/feeds/cells/<sheet-id>/<tab-number>/public/full?alt=json

When a GET request is sent to the relevant link via Postman and/or an internet browser, a response similar to the one illustrated in the following image will be returned.

Converting JSON Content into a Table Format

So, if we have a JSON content, how can we convert it into a table format? Numerous online tools allow you to convert JSON content into various formats (CSV, TSV, XLSX, etc.). If you’d like to perform the operation once, you can use Google Apps Script² to carry out the tasks³.

For this purpose, either paulgambill/import_json_appsscript.js or bradjasper/ImportJSON can be used.

Google Sheets and Integrations

You can transfer Google Sheets data to various services or convert data from different services into table format using automation services such as IFTTT, Zapier, Power Automate, and Tonkean, or development environments such as Pipedream.

Data Visualization

Google Sheets spreadsheets can be easily defined as a data source in Looker Studio.

With this process, you can visualize data either directly through forms or by transferring JSON content, and then export the data again in JSON format.

*[CSV]: Comma-separated values
*[JSON]: JavaScript Object Notation

Footnotes

1. Clark Jason Ngo. (2019). How to use Google Sheets as a JSON Endpoint ↩ ↩²
2. Ben Collins. (2018). Google Apps Script: A Beginner’s Guide ↩
3. Meelad Mashaw. (2020). 3 Best Ways To Import JSON To Google Sheets ↩