Populate Google Spreadsheet cells with JSON API data, conversion tutorial

-
For this tutorial we will populate Google Spreadsheets cells with API data, target columns and rows on it.

First check the archived page for the ImportJSON documentation at https://rawgit.com/bradjasper/ImportJSON/master/archive/blog.fastfedora.com/projects/import-json.html

Next open up the latest version of ImportJSON script found at this repo link https://github.com/bradjasper/ImportJSON

Open ImportJSON.gs, click raw > copy the whole script

Now on google spreadsheet, create new spreadsheet or open existing one which you'll populate data with

Click Tools > Script Editor



It will popup a new tab/window, delete the default script inside


Then paste the ImportJSON code you've copied from the repo

Save and name the project

Now back to the spreadsheet click cell A1, from the documentation we know how to call the function ImportJSON

I'll be using this sample JSON API link https://api.github.com/users/hadley/repos

on cell A1 value paste

=ImportJSON("https://api.github.com/users/hadley/repos", "/id,/fullname,/owner/html_url,/owner/avatar_url", "noInherit, noTruncate")

**You can add all other data on the 2nd parameter, for nested data you will add them by full url like how I did with /owner/avatar_url since avatar_url is under owner, etc

After pasting the value for cell A1, it will take time to load data depending on how big or how many columns you'll have on yours so just be patient... after that you will see the data populate, enjoy! ;)


Comments

Post a Comment

Popular posts from this blog

Setting up TMS Web Planner and Access Database [TMS Tut Part 1]

-
First: create the database named Planner.accdb, create a table named Scheduler inside with the ff fields: Field:  ID             Type: AutoNumber Field:  Subject     Type: Text Field:  Notes       Type: Text Field:  StartTime  Type: Date/Time Field:  EndTime   Type: Date/Time We will add necessary fields later. As for now that would do fine for a prototype. Second: create an empty C# web application project in Visual Studio. Make sure your TMS Web Planner Controls has already been integrated with Visual Studio Toolbox. On your solution explorer right click and create a new folder named "Planner", add an empty ASP item on that folder name it "Planner.aspx" Right-click on Planner.aspx file and select design. Drag and drop an Access DataSource object under Toolbox on Visual Studio. Now configure the Access DataSource and connect it to the Planner.accdb file. I suggest you put the acc...
Read more

Converting JSON API returned data into 2016 SQL table rows

-
Image
To start the setup you must have the 2016 version of SQL Server(or higher version) Create the table beforehand along w/ desired columns(sample columns added) Fetch API data w/ POSTMAN chrome extension tool or simply use your API fetching custom app to retrieve the data(browser will do too) In SQL Server 2016 create new query to your db > table Copy > Paste the following script DECLARE @json NVARCHAR(MAX) SET @json = N'[<json>]' INSERT INTO <table> SELECT * FROM OPENJSON ( @json ) WITH ( <fields> ) where <json> is the returned data from POSTMAN/browser, <table> is the SQL table to which you want the data to be added, <fields> would be the matching fields we add from JSON to SQL column an example for this with exact sample values would be: DECLARE @json NVARCHAR(MAX) SET @json = N'[ { "Order": { "Number":"SO43659", "Date":"2011-05-31...
Read more

OOTB Valvrave III Hikaminari 1/144 Review

-
Image
This week I finally got a hold of this Valvrave kit: Now I'm gonna show you a quick review on this amazing mecha. First I've built the kit out of the box a day ago, so now I'm gonna take off the removable parts and let you peak through of what it looks like ;) By the way here are the size comparisons, size against a 1/144 G-Exes: Pretty big compared to most 1/144 right? Let see the size compared to an 1/100 MG Aile Strike: Aha! the Aile Strike Head is much taller than the Hikaminari Head, but the overall kit is pretty much the same or taller than the Aile. Sweet! :) Alright! Now we're moving on to the parts! yay! I've removed the important pieces so you can see which of them can be detached/attached. To enumerate you have 2x Armstronger, 2x Arms, 2x Legs, 2x Chain Saucer, 2x Dai Arms, 1x Head, 1x Chest, 1x Hip We will start checking out the head first ;) As you can see it doesn't have too many pieces. The left and right face are to be snap f...
Read more