export nested JSON from GCS into Spreadsheet

up vote
-2
down vote

favorite

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

edited 2 days ago

asked Nov 19 at 15:05

Mira

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 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
2 days ago

add a comment |

up vote
-2
down vote

favorite

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

edited 2 days ago

asked Nov 19 at 15:05

Mira

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 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
2 days ago

add a comment |

up vote
-2
down vote

favorite

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

edited 2 days ago

asked Nov 19 at 15:05

Mira

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

json google-sheets google-bigquery google-cloud-storage ndjson

edited 2 days ago

asked Nov 19 at 15:05

Mira

edited 2 days ago

asked Nov 19 at 15:05

Mira

edited 2 days ago

asked Nov 19 at 15:05

Mira

asked Nov 19 at 15:05

Mira

asked Nov 19 at 15:05

Mira

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 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
2 days ago

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 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
2 days ago

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 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
2 days ago

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

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 at 14:33

Alex Riquelme

36718

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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

add a 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',
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');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

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.

answered Nov 20 at 14:33

Alex Riquelme

36718

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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

add a comment |

up vote
0
down vote

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.

answered Nov 20 at 14:33

Alex Riquelme

36718

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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

add a comment |

up vote
0
down vote

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.

answered Nov 20 at 14:33

Alex Riquelme

36718

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.

answered Nov 20 at 14:33

Alex Riquelme

36718

answered Nov 20 at 14:33

Alex Riquelme

36718

answered Nov 20 at 14:33

Alex Riquelme

36718

answered Nov 20 at 14:33

Alex Riquelme

36718

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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

add a 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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

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
2 days ago

@Mira Can you share with me a small sample of your exported JSON and your CSV?
– Alex Riquelme
17 hours ago

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk