Sheets™ to JSON
OVERVIEW
Sheets™ to JSON is a Google Sheets™ Add-on that converts JSON into named ranges in a Google Sheet™ and vice versa. It features a Github Gist integration which allows the user to instantly publish exported JSON to a publicly-hosted location.
Google Sheets™ is a trademark of Google LLC.
LIMITATIONS
JSON key names cannot contain characters other than letters, numbers, and underscores, and must not be longer than 200 characters.
TUTORIAL
FUNCTIONS
For ease of use it’s recommended that you allow popups from https://docs.google.com.
Import (current sheet) - overwrites the current sheet with a representation of the JSON object present in the first cell. Its highly recommended that this is used on an otherwise blank and unformatted sheet - existing formatting and name ranges in the sheet can result in unwanted data when you export later.
Export (current sheet) - parses the JSON representation present in the current sheet and displays the JSON in a textbox. It does not modify the sheet.
Export to PUBLIC Gist (current sheet) - parses the JSON representation present in the current sheet and posts it publicly to Github Gist as a new file named “sheets.json”. You must authenticate with Github for this function to work. See an example output here.
Export (‘JSON’ sheet) - identical to Export (current sheet), but shows the output for the sheet named “JSON” instead.
Export to PUBLIC Gist (‘JSON’ sheet) - identical to Export to PUBLIC Gist (current sheet), but publicly posts the output for the sheet named “JSON” instead.
Revoke Github access - deletes the cached Github access token and opens the Github Authorized OAuth Apps page, where you can revoke Sheets™ to JSON’s access to your Github account.
[DEBUG] Print range type - displays the data type of the selected cell. Can be used to debug issues where the exported data is the wrong type. To correct the data type, you can use Sheets™ functions like =TEXT().
[DEBUG] Name selected range - allows you to set named ranges that will be parsed into JSON when exporting. Only recommended if you’ve read the add-on source code.
[DEBUG] Print range tree (current sheet) - displays the intermediate representation that the add-on parses named ranges into before converting into JSON. Only recommended if you’ve read the add-on source code.
[DEBUG] Purge named ranges - deletes all named ranges in the sheet. It’s usually preferable to delete the sheet that was written to by the add-on than to use this function. Only recommended if you’ve read the add-on source code.
Help - opens this webpage.
Privacy policy - opens this webpage.
SOURCE
The source code for Sheets™ to JSON can be found here. This code may not always reflect the published add-on. If you don’t wish to run code you haven’t read, there are instructions in the source code for setting it up yourself.
PRIVACY
With the exception of the Github Gist integration, Sheets™ to JSON does not share your data in any way with any party other than yourself.
DATA READ FROM YOUR SHEETS
Only the sheet you are currently looking at (or, for some functions, the sheet named “JSON”) is examined when the app is invoked, and even then only the contents of named ranges on that sheet are inspected. Functions that read from the sheet named “JSON” are clearly marked - if you have no sheet named “JSON” in the current spreadsheet, they simply won’t work.
Except for the functions that include the text “to PUBLIC Gist”, the data read from your sheet is not stored or shared by the app. It is processed into JSON and then displayed to you, and when you close the JSON window no record remains of the data.
DATA PUBLISHING VIA GITHUB
Some functions allow users to instantly publish JSON based on the named ranges in the current sheet to a publicly-hosted Github Gist. Data posted to Github falls under Github’s privacy policy. The Gists posted by Sheets™ to JSON can be found and viewed by anyone, as they are public.
You do not have to give Sheets™ to JSON Github access in order to use the add-on - only the “Export to PUBLIC Gist” menu items require it. Sheets™ to JSON does not view or modify any of your Github data - it only publishes new Gists.
Should you choose to use it, this functionality involves an OAuth integration with Github. After you have allowed access to Github, Sheets™ to JSON stores an access token for in Google’s PropertiesService User Properties. This access token can only be read when Sheets™ to JSON is used by your account - if you share a sheet with someone else, they won’t be able to post Github Gists on your behalf.
You can revoke Github access at any time via the Github OAuth Apps page. You can also find this page via Add-ons —> Sheets™ to JSON —> Revoke Github access.
SECURITY
GITHUB INTEGRATION
If you choose to use the Github Gist integration, Sheets™ to JSON will only request the “gist” Github scope (which you can verify by reading the Github OAuth screen). The access token provided to Sheets™ to JSON is stored in Apps Script PropertiesService. The Github OAuth integration is handled by the Google-authored OAuth2 Apps Script library. The minimal Sheets™ to JSON code required to implement the Github integration can be inspected here. This github repo may not always reflect exactly the contents of the published add-on.
CONTACT
If you need help, find a security flaw in the code, or have questions please reach out at sheets-to-json@googlegroups.com.
TERMS OF SERVICE
By using this addon you agree to be bound by these Terms:
This addon allows you to manipulate your data and publish it to Github Gist. You are responsible for any and all outcomes, including loss or leak of data, that arise from use of this addon. By using the addon, you agree that the addon creator has no responsibility for loss of data, accidental publishing of data, or any other loss or damage that arises from use of the addon.
This addon makes API calls and links to third-party websites & services. The addon creator has no control over and no responsibility for the content, policies, and practices of these third parties. The addon creator shall not be responsible or liable, directly or indirectly, for any damage or loss caused or alleged to be caused by or in connection with use of or reliance on any content, goods, or services available through any such websites & services.
The addon creator reserves the right to modify or replace these Terms at any time. If a revision is major, notice of the change will be displayed upon your next use of the application. What constitutes a major revision will be determined by the addon creator.
Google Sheets™ is a trademark of Google LLC.