Simple way to transform any Google Sheet to JSON

Solution is really easy, but with one big drawback - the sheet needs to accessed publicly on the Internet. In my case this is not a permanent feature - I just have to move data from one source to another one, but I would not recommend this solution if you deal with sensitive data.

Prepare the sheet

Go to File > Publish to the web and select Entire Document > Web Page. That's all here, just close the window.

Next, copy the sheet id from the URL in the browser.

The saviour

gsx2json is a tool which does all the magic for us. Please go to the project website and buy a coffee to the author if it helped you. It saved me a ton of time!

Get the data

I use C# but the code is quite simple. Just create the url with the sheet id and get the JSON.

// using System.Net;
var sheetId = "1zpss4g850wpb9YltdzefCyvl2Vl4gDZ-Ip4Hk-dQjOk";
var uri = new Uri($"http://gsx2json.com/api?id={sheetId}");
string json; 

using (var wc = new WebClient())
{
    json = await wc.DownloadStringTaskAsync(uri);
}

Here's the link to the API if you'd like to review the JSON created by gsx2json.

Transform the data

Now, when you got the data, do whatever it has to be done. Here is an example of extracting rows from the JSON response.

// using Newtonsoft.Json.Linq;
var tmp = JObject.Parse(json)["rows"];
var players = tmp.ToObject<Player[]>();

Cleaning up

Remember to unpublish the sheet from the Internet. And that's all!