Hosting Data Using Google Sheets

For simple tabular data, Google Sheets is a good way to manage certain content types.

You and others who you grant access to the sheet can update that content anytime, and the changes will immediately reflect in your website.

Supported Formats

Presentation-oriented formats;

• _pdf - PDF document, including your sheet's styling

Download-oriented formats;

• _xlsx - Microsoft Excel ( XLSX )
• _ods - OpenDocument spreadsheet format
• _zip - ZIP archive ( untested )

Data-oriented formats;

• _csv - Comma Separated File (CSV), which is a raw data format
• ‍_tsv - Tab Separated File ( TSV )

Integration

Download the Document

If you simply want users to be able to download the current content, you can provide them a download link to the format you want. See below.

IFRAME Embed

If you want to display the actual document content in your page, exactly as it appears in Google Docs, you can use an IFRAME to embed it.

Direct JavaScript Embedding

You can also use JavaScript with the data-oriented formats to retrieve the content, parse it, and then integrate that content into your webpage.

Creating the Link

Open your spreadsheet in Google Sheets to see the spreadsheet ID in the URL. You'll use that to create a URL in this format;

_{https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?format=csv}

Note the _?format=csv at the end.

Google Sheets supports several export types. You can specify the one you want as in the _format parameter.

• _csv - Comma Separated File (CSV), which is a raw data format
• _pdf - PDF document, including your sheet's styling
• _xlsx - Microsoft Excel ( XLSX )
• _ods - OpenDocument spreadsheet format
• _tsv - Tab Separated File ( TSV )
• _zip - ZIP archive ( untested )

With any of these, f you want to export a specific sheet ( and not the whole workbook ), you can view that sheet and note the gid parameter in the browser's URL.

You can add that to your above URL, e.g.

_{https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?format=pdf&gid=SHEET_ID}

Notes

Replace _{SPREADSHEET_ID} with the ID of your Google Spreadsheet document. This ID is found in the URL when viewing the sheet.

If you're using _gid, make sure to replace SHEET_ID with that sheet's gid

Keep in mind that these URLs will prompt a download of the exported file.

The person using the URL must have the appropriate permissions to access the Google Sheets document. If the document is private, they will need to be logged into an account that has been granted access. If the document is public, anyone with the link can download the exported file.

Complex spreadsheets with multiple sheets, formulas, or specific formatting may not export perfectly to every format. Test to make sure the exported file meets your requirements.

If your browser recognizes the format as something it can display, e.g. html, it may attempt to simply show the file. If you'd like to force a download, you can add a custom _download="" attribute to your link in Webflow.

JSON

If you're a developer, you want data, and here that means JSON. JSON would allow you to manipulate the spreadsheet data however you like.

There appear to be a few promising approaches to this;

This is not fully tested.

doGet & Apps Script

Google Sheets does not natively support exporting to JSON format directly from the user interface or through a URL parameter. However, there are ways to convert Google Sheets data to JSON dynamically using Google Apps Script or the Google Sheets API.

1. Click on "Extensions" in the menu, then "Apps Script."
2. Delete any code that's there, then paste in the javascript below.
3. Click on "Deploy" > "New deployment."
4. In the dialog that appears, select "Web app" for the deployment type.
5. Give your project a name and set "Who has access" to "Anyone."
6. Click "Deploy." You'll get a URL that you can use to access the JSON data.

This script takes the data from the active sheet and converts it to JSON. The _doGet function is a special function in Google Apps Script that gets called when someone accesses the web app URL.

Script;

_{function doGet(e){}

 _{var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();}

 _{var data = sheet.getDataRange().getValues();}

 _{var jsonData = JSON.stringify(data);}

 _{return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON);}

_}

Using Google Sheets API v4

You'll need a Google API key to use this endpoint.

_{var url = 'https://sheets.googleapis.com/v4/spreadsheets/' +           spreadsheet_id + '/values/' + tab_name +           '?alt=json&key=' + api_key;($.getJSON(url, 'callback=?')).success(function(data) {  // ...};}

Ref: Did Google Sheets stop allowing json access?

Google Visualization API ( GViz )

Here's a different way that does not require a Google API key.

The basic URL is;

_{https://docs.google.com/spreadsheets/d/{spreadsheetId}/gviz/tq}

However the results require some script manipulation.

Did Google Sheets stop allowing json access?https://stackoverflow.com/a/71334523

Did Google Sheets stop allowing json access?https://stackoverflow.com/a/68948211

‍

‍