This content originally appeared on DEV Community and was authored by Krzysztof Kopieczek
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!
This content originally appeared on DEV Community and was authored by Krzysztof Kopieczek
Krzysztof Kopieczek | Sciencx (2021-04-23T20:53:02+00:00) Simple way to transform any Google Sheet to JSON. Retrieved from https://www.scien.cx/2021/04/23/simple-way-to-transform-any-google-sheet-to-json/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.