Convert .csv file to .xls using Google AppScript
up vote
0
down vote
favorite
I have a use case wherein I have to fetch BigQuery result and store it in an excel file. Since BigQuery by default does not have any such provision, I was thinking of exporting the query result in CSV format and then convert it to excel format using Google AppScript. We tried doing this using Sheet-js but it was not working in AppScript.
We also tried using Google Spreadsheet but it seems there is a limitation of 10 million cells per workbook. Please correct me if I'm wrong.
Can someone please suggest what approach I must take to achieve this. Any kind of help will be appreciated.
Thanks.
excel
google-apps-script 
google-bigquery asked Nov 14 at 9:22
rish0097
211112
add a comment |
up vote
0
down vote
favorite
I have a use case wherein I have to fetch BigQuery result and store it in an excel file. Since BigQuery by default does not have any such provision, I was thinking of exporting the query result in CSV format and then convert it to excel format using Google AppScript. We tried doing this using Sheet-js but it was not working in AppScript.
We also tried using Google Spreadsheet but it seems there is a limitation of 10 million cells per workbook. Please correct me if I'm wrong.
Can someone please suggest what approach I must take to achieve this. Any kind of help will be appreciated.
Thanks.
excel
google-apps-script google-bigquery asked Nov 14 at 9:22
rish0097
211112
2
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
1
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
values like '00014' to '14'Use strings
– TheMaster
Nov 14 at 14:53
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a use case wherein I have to fetch BigQuery result and store it in an excel file. Since BigQuery by default does not have any such provision, I was thinking of exporting the query result in CSV format and then convert it to excel format using Google AppScript. We tried doing this using Sheet-js but it was not working in AppScript.
We also tried using Google Spreadsheet but it seems there is a limitation of 10 million cells per workbook. Please correct me if I'm wrong.
Can someone please suggest what approach I must take to achieve this. Any kind of help will be appreciated.
Thanks.
excel
google-apps-script google-bigquery asked Nov 14 at 9:22
rish0097
211112
I have a use case wherein I have to fetch BigQuery result and store it in an excel file. Since BigQuery by default does not have any such provision, I was thinking of exporting the query result in CSV format and then convert it to excel format using Google AppScript. We tried doing this using Sheet-js but it was not working in AppScript.
We also tried using Google Spreadsheet but it seems there is a limitation of 10 million cells per workbook. Please correct me if I'm wrong.
Can someone please suggest what approach I must take to achieve this. Any kind of help will be appreciated.
Thanks.
excel
google-apps-script google-bigquery excel
google-apps-script google-bigquery asked Nov 14 at 9:22
rish0097
211112
asked Nov 14 at 9:22
rish0097
211112
edited Nov 14 at 15:04
asked Nov 14 at 9:22
rish0097
211112
asked Nov 14 at 9:22
rish0097
211112
asked Nov 14 at 9:22
rish0097
211112
211112
2
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
1
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
values like '00014' to '14'Use strings
– TheMaster
Nov 14 at 14:53
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09
add a comment |
2
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
1
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
values like '00014' to '14'Use strings
– TheMaster
Nov 14 at 14:53
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09
2
2
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
1
1
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
values like '00014' to '14' Use strings– TheMaster
Nov 14 at 14:53
values like '00014' to '14' Use strings– TheMaster
Nov 14 at 14:53
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Behavior you describe (converts values like '00012' to '12') is seen when you simply Open csv file!
Instead you can Import Data from File into Excel and if you do so you have ability to control how this will happen
So, steps to try as below (based on the version of Excel I have - it can be slightly different in yours)
Open Excel with New Workbook >> Data >> Get Data >> From File >>
From Text/CSV >> Select your File & Import >> Edit >> ...
Last action in previous list will open Power Query Editor where you can set types of your columns as well as control many other aspects
for example - right click on the column and change type to text, etc.
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Behavior you describe (converts values like '00012' to '12') is seen when you simply Open csv file!
Instead you can Import Data from File into Excel and if you do so you have ability to control how this will happen
So, steps to try as below (based on the version of Excel I have - it can be slightly different in yours)
Open Excel with New Workbook >> Data >> Get Data >> From File >>
From Text/CSV >> Select your File & Import >> Edit >> ...
Last action in previous list will open Power Query Editor where you can set types of your columns as well as control many other aspects
for example - right click on the column and change type to text, etc.
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
add a comment |
up vote
0
down vote
Behavior you describe (converts values like '00012' to '12') is seen when you simply Open csv file!
Instead you can Import Data from File into Excel and if you do so you have ability to control how this will happen
So, steps to try as below (based on the version of Excel I have - it can be slightly different in yours)
Open Excel with New Workbook >> Data >> Get Data >> From File >>
From Text/CSV >> Select your File & Import >> Edit >> ...
Last action in previous list will open Power Query Editor where you can set types of your columns as well as control many other aspects
for example - right click on the column and change type to text, etc.
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
add a comment |
up vote
0
down vote
up vote
0
down vote
Behavior you describe (converts values like '00012' to '12') is seen when you simply Open csv file!
Instead you can Import Data from File into Excel and if you do so you have ability to control how this will happen
So, steps to try as below (based on the version of Excel I have - it can be slightly different in yours)
Open Excel with New Workbook >> Data >> Get Data >> From File >>
From Text/CSV >> Select your File & Import >> Edit >> ...
Last action in previous list will open Power Query Editor where you can set types of your columns as well as control many other aspects
for example - right click on the column and change type to text, etc.
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
Behavior you describe (converts values like '00012' to '12') is seen when you simply Open csv file!
Instead you can Import Data from File into Excel and if you do so you have ability to control how this will happen
So, steps to try as below (based on the version of Excel I have - it can be slightly different in yours)
Open Excel with New Workbook >> Data >> Get Data >> From File >>
From Text/CSV >> Select your File & Import >> Edit >> ...
Last action in previous list will open Power Query Editor where you can set types of your columns as well as control many other aspects
for example - right click on the column and change type to text, etc.
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
answered Nov 14 at 17:01
Mikhail Berlyant
54.5k43368
54.5k43368
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
add a comment |
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
Hi Mikhail I know that. But the the thing is that we need to automate this process and provide an excel to the end user. How to control the data values using code is where I'm stuck. And the end user is only comfortable with having an excel.
– rish0097
Nov 14 at 17:10
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53296728%2fconvert-csv-file-to-xls-using-google-appscript%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
2
Excel supports opening CSV files, so why go to all this effort?
– Graham Polley
Nov 14 at 11:12
1
Since XLS is a Microsoft proprietary binary format, trying to create "excel format" is probably beyond most of the participants of this forum. And as @GrahamPolley mentions just open the CSV with Excel.
– TheWizEd
Nov 14 at 11:57
values like '00014' to '14'Use strings– TheMaster
Nov 14 at 14:53
Hi @GrahamPolley Yes but it converts values like '00012' to '12' when opening in excel. And this is a strict requirement that we have for reporting purposes hence we could not use .csv.
– rish0097
Nov 14 at 15:09