-1

I have a nested NDJSON file that I exported from BQ into Google Cloud Storage. From there I would like to open it in Spreadsheet again as a nested table.

I see a lot of Appscripts to import JSON files but none are for files stored in GCS.

What would be the best solution to open the data table in spreadsheet?

the csv file I see when I use the tool suggested by Alex

This is the NDJSON example: 

    {"page":"/xxxx","country":"DE","pageviews":"72136","daily_peak_pageviews":"5465","daily_peak_users":"3118","users_unique":"37763","SEO":true,"campaign_info":[{"channel_group":"Referral","users_c":"16","pageviews_c":"17","title":"404"},{"channel_group":"Social","users_c":"2255","pageviews_c":"3839","title":"OK"},{"channel_group":"other","users_c":"33185","pageviews_c":"63320","title":"OK"},{"channel_group":"Referral","users_c":"316","pageviews_c":"556","title":"OK"},{"channel_group":"Paid","users_c":"47","pageviews_c":"49","title":"404"},{"channel_group":"Paid","users_c":"1088","pageviews_c":"1706","title":"OK"},{"channel_group":"other","users_c":"1888","pageviews_c":"2517","title":"404"},{"channel_group":"Social","users_c":"100","pageviews_c":"132","title":"404"}]}
{"page":"/yyy","country":"DE","pageviews":"67576","daily_peak_pageviews":"5390","daily_peak_users":"2843","users_unique":"32772","SEO":true,"campaign_info":[{"channel_group":"other","users_c":"7","pageviews_c":"10","title":"404"},{"channel_group":"other","users_c":"30951","pageviews_c":"64345","title":"OK"},{"channel_group":"Paid","users_c":"782","pageviews_c":"1303","title":"OK"},{"channel_group":"Referral","users_c":"265","pageviews_c":"467","title":"OK"},{"channel_group":"Social","users_c":"889","pageviews_c":"1450","title":"OK"},{"channel_group":"Paid","users_c":"1","pageviews_c":"1","title":"404"}]}
{"page":"/zzz","country":"DE","pageviews":"7558","daily_peak_pageviews":"619","daily_peak_users":"331","users_unique":"4117","SEO":true,"campaign_info":[{"channel_group":"other","users_c":"7","pageviews_c":"14","title":"404"},{"channel_group":"Paid","users_c":"38","pageviews_c":"70","title":"OK"},{"channel_group":"other","users_c":"3987","pageviews_c":"7309","title":"OK"},{"channel_group":"Paid","users_c":"1","pageviews_c":"1","title":"404"},{"channel_group":"Referral","users_c":"18","pageviews_c":"26","title":"OK"},{"channel_group":"Social","users_c":"70","pageviews_c":"138","title":"OK"}]}
{"page":"hdhh","country":"DE","pageviews":"3616","daily_peak_pageviews":"336","daily_peak_users":"206","users_unique":"2131","campaign_info":[{"channel_group":"Social","users_c":"267","pageviews_c":"379","title":"OK"},{"channel_group":"Paid","users_c":"776","pageviews_c":"1394","title":"OK"},{"channel_group":"other","users_c":"1089","pageviews_c":"1814","title":"OK"},{"channel_group":"Referral","users_c":"17","pageviews_c":"24","title":"OK"},{"channel_group":"other","users_c":"2","pageviews_c":"5","title":"404"}]}
{"page":"/ethehh","country":"DE","pageviews":"1394","daily_peak_pageviews":"322","daily_peak_users":"294","users_unique":"1232","campaign_info":[{"channel_group":"Paid","users_c":"61","pageviews_c":"67","title":"OK"},{"channel_group":"Social","users_c":"271","pageviews_c":"301","title":"OK"},{"channel_group":"other","users_c":"3","pageviews_c":"5","title":"404"},{"channel_group":"Referral","users_c":"10","pageviews_c":"10","title":"OK"},{"channel_group":"other","users_c":"888","pageviews_c":"1011","title":"OK"}]}

and this is the csv example:

page,country,pageviews,daily_peak_pageviews,daily_peak_users,users_unique,SEO,campaign_info/0/channel_group,campaign_info/0/users_c,campaign_info/0/pageviews_c,campaign_info/0/title,campaign_info/1/channel_group,campaign_info/1/users_c,campaign_info/1/pageviews_c,campaign_info/1/title,campaign_info/2/channel_group,campaign_info/2/users_c,campaign_info/2/pageviews_c,campaign_info/2/title,campaign_info/3/channel_group,campaign_info/3/users_c,campaign_info/3/pageviews_c,campaign_info/3/title,campaign_info/4/channel_group,campaign_info/4/users_c,campaign_info/4/pageviews_c,campaign_info/4/title,campaign_info/5/channel_group,campaign_info/5/users_c,campaign_info/5/pageviews_c,campaign_info/5/title,campaign_info/6/channel_group,campaign_info/6/users_c,campaign_info/6/pageviews_c,campaign_info/6/title,campaign_info/7/channel_group,campaign_info/7/users_c,campaign_info/7/pageviews_c,campaign_info/7/title
/xxxx,DE,72136,5465,3118,37763,true,Referral,16,17,404,Social,2255,3839,OK,other,33185,63320,OK,Referral,316,556,OK,Paid,47,49,404,Paid,1088,1706,OK,other,1888,2517,404,Social,100,132,404
/yyy,DE,67576,5390,2843,32772,true,other,7,10,404,other,30951,64345,OK,Paid,782,1303,OK,Referral,265,467,OK,Social,889,1450,OK,Paid,1,1,404,,,,,,,,
/zzz,DE,7558,619,331,4117,true,other,7,14,404,Paid,38,70,OK,other,3987,7309,OK,Paid,1,1,404,Referral,18,26,OK,Social,70,138,OK,,,,,,,,
hdhh,DE,3616,336,206,2131,,Social,267,379,OK,Paid,776,1394,OK,other,1089,1814,OK,Referral,17,24,OK,other,2,5,404,,,,,,,,,,,,
/ethehh,DE,1394,322,294,1232,,Paid,61,67,OK,Social,271,301,OK,other,3,5,404,Referral,10,10,OK,other,888,1011,OK,,,,,,,,,,,,
Mira
  • 11
  • 1
  • I'm not sure if this will entirely answer the question since it actually bypasses GCS entirely, but here's a tutorial on how to export BigQuery tables to Sheets https://cloud.google.com/blog/products/gcp/how-to-crunch-your-business-data-from-sheets-in-bigquery – Lisa Yin Nov 19 '18 at 19:53
  • Thanks Liksa, but this is not what I am looking for. This explains how to query data from a sheet in BQ but I want to export data from BQ to a sheet. – Mira Nov 24 '18 at 07:22

1 Answers1

0

I found some scripts to load json files into a Google SpreadSheet, but all of them need to be loaded using a url, so the steps to get a public link to your JSON file in GCS are:

  1. Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
  2. click into "edit permissions"
  3. Click into "Add item"
  4. in "ENTITY" choose "User", then en "NAME" type "allUsers" and in "ACCESS" choose "Reader".

Now you have an external link to load your JSON using some scripts, like this one or this other one, but you need to edit the JSON file or the code a bit.

Another solution (and the easiest one), is to convert the JSON file into CSV using this tool and then, import the CSV into Google SpreadSheet clicking into "File" -> "import" -> "Upload" and then select your CSV file.

Alex Riquelme
  • 1,475
  • 7
  • 14
  • Thanks a lot for your suggestions. The tool you suggested in your last paragraph doesn't work for me unfortunately, either it is because of the newline-delimited JSON format BQ provides or something else but the output csv does not show me the nested schema and looks different than the original table(the repeated fields are simply written into columns but not in rows below each other) – Mira Nov 24 '18 at 07:08
  • @Mira Can you share with me a small sample of your exported JSON and your CSV? – Alex Riquelme Nov 26 '18 at 11:45
  • sure, I added the samples above – Mira Nov 27 '18 at 16:55
  • I understand now the problem that you are facing. The JSON and the CSV looks correct, but, as I understand, you want the rows to be repeated as many times as entries you have in the campaign_info array. To do that, you can use a script to transform those parameters into new lines. I have no time to develop the script, I'm truly sorry. – Alex Riquelme Nov 28 '18 at 15:12
  • You could use [Cloud Dataprep](https://cloud.google.com/dataprep/) to configure your CSV for proper importing into sheets. – Jofre Dec 02 '18 at 21:24