Mixing
export nested JSON from GCS into Spreadsheet
-2
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,,,,,,,,,,,,json google-sheets google-bigquery google-cloud-storage ndjson
asked Nov 19 '18 at 15:05
Mira Mira
11
add a comment |
-2
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,,,,,,,,,,,,json google-sheets google-bigquery google-cloud-storage ndjson
asked Nov 19 '18 at 15:05
Mira Mira
11
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 cloud.google.com/blog/products/gcp/…
– 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 7:22
add a comment |
-2
-2
-2
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,,,,,,,,,,,,json google-sheets google-bigquery google-cloud-storage ndjson
asked Nov 19 '18 at 15:05
Mira Mira
11
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,,,,,,,,,,,,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,,,,,,,,,,,,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,,,,,,,,,,,,json google-sheets google-bigquery google-cloud-storage ndjson
json google-sheets google-bigquery google-cloud-storage ndjson
asked Nov 19 '18 at 15:05
Mira Mira
11
asked Nov 19 '18 at 15:05
Mira Mira
11
edited Nov 27 '18 at 17:11
Mira
asked Nov 19 '18 at 15:05
Mira Mira
11
asked Nov 19 '18 at 15:05
Mira Mira
11
asked Nov 19 '18 at 15:05
Mira Mira
11
11
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 cloud.google.com/blog/products/gcp/…
– 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 7:22
add a comment |
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 cloud.google.com/blog/products/gcp/…
– 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 7:22
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 cloud.google.com/blog/products/gcp/…
– Lisa Yin
Nov 19 '18 at 19:53
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 cloud.google.com/blog/products/gcp/…
– 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 7:22
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 7:22
add a comment |
1 Answer
1
active
oldest
votes
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:
- Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
- click into "edit permissions"
- Click into "Add item"
- 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.
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
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 7: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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377427%2fexport-nested-json-from-gcs-into-spreadsheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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:
- Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
- click into "edit permissions"
- Click into "Add item"
- 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.
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
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 7: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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
|
show 1 more comment
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:
- Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
- click into "edit permissions"
- Click into "Add item"
- 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.
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
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 7: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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
|
show 1 more comment
0
0
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:
- Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
- click into "edit permissions"
- Click into "Add item"
- 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.
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
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:
- Go to your Google Cloud Storage bucket and then in your json file click in the three dots at the right.
- click into "edit permissions"
- Click into "Add item"
- 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.
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
answered Nov 20 '18 at 14:33
Alex RiquelmeAlex Riquelme
54429
54429
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 7: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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
|
show 1 more comment
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 7: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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
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 7:08
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 7: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
@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
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
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
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
Okay, thanks for your help and reply anyway.
– Mira
Nov 30 '18 at 7:45
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377427%2fexport-nested-json-from-gcs-into-spreadsheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 cloud.google.com/blog/products/gcp/…
– 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 7:22