【GAS】スプレッドシートのシートごとにエクセルファイルを生成

Question

前提

GAS超初心者です。
ご教授いただけますと大変幸いです。

実現したいこと

スプレッドシートのシートごとにエクセルファイルを生成し、GoogleDriveに保存したい。

エラー

なぜかコードのようなものが書かれたHTMLファイルが生成されてしまう。

参考サイト

https://note.com/taraco123/n/n53b662bcf8fc
（これはPDF生成版）

function myFunction() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const key = ss.getId();

const sheets = ss.getSheets();

const token = ScriptApp.getOAuthToken();

const folder = DriveApp.getFolderById("★フォルダIDを記載");

sheets.forEach(function(sheet){

const gid = sheet.getSheetId();

const url = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + gid + "&exportFormat=xlsx”;

const options = {
    headers:{"Authorization":"Bearer " +  token} 
   }

   const response = UrlFetchApp.fetch(url,options);

   const blob = response.getBlob();

   blob.setName(sheet.getName());

   folder.createFile(blob);

 });
}

Comments

[k.a_teratail]

> const url = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + gid + "&exportFormat=xlsx;
こちら、末尾に「"」が無いのと

> }
不要な閉じカッコで

構文エラーになりますが、質問者様の方では問題無く実行できているのでしょうか？

[shirogohan]

「"」は記載漏れでした。失礼しました。不要な閉じカッコはどちらになりますでしょうか…？

[k.a_teratail]

コードに関数名を追加したので、不要な閉じカッコについては削除する必要はありません。

[k.a_teratail]

ちなみにまだエラーは出ていました。
> "&exportFormat=xlsx”
最後が「"」ではないので構文エラーになっていました。

Answer 1

こちらのサイトのコードを参考にしてみてはどうでしょうか？
https://www.ka-net.org/blog/?p=11966

// blob.setName(sheet.getName());
blob.setName(sheet.getName() + ".xlsx");

---

実施前

Googleドライブのフォルダ

スプレッドシートの各シート

シート85	シート50	シート53

コード実行

実行結果

実施後確認

Googleドライブのフォルダ

---

追加実施前

Googleドライブのフォルダ

スプレッドシートの各シート

QUERY関数	IMPORTRANGE関数	QUERY+IMPORTRANGE関数

追加実施

実行結果

追加実施後

Googleドライブのフォルダ

Comments

[shirogohan]

以前こちらのサイトも参照してみたのですが、初心者の為こちらの方の書くコードがあまりよく理解できず…

記載したサイトでは解説もあり＋今回の目的であるシートごとの出力が出来ると思い参照した次第です。

その記載コード部分もそのように修正してみたのですが、やはりエクセルファイルではなく、HTMLファイルが出力されてしまいました…

[k.a_teratail]

blob.setName(sheet.getName());
を
blob.setName(sheet.getName() + ".xlsx");
に書き換えてもHTMLファイルが出力されますか？

[k.a_teratail]

こちらで実施した結果を追記しました。
実現したい結果になっているか、ご確認の方、よろしくお願いします。

[shirogohan]

ご丁寧に本当にありがとうございます！これがまさにやりたいことなのですが、blob.setName(sheet.getName() + ".xlsx");にしても変わらずエクセルファイルではなく、HTMLファイルが出力されてしまいました…。シートが15枚ほどあるのが原因でしょうか（ ; ; ）ちなみにHTMLファイルにはよくわからないコード？文字の羅列が入力されています…

[k.a_teratail]

> シートが15枚ほどあるのが
こちらでも15枚で試しましたが、xlsx形式で問題なく出力されました。

もしかすると、スプレッドシートの内容が関係あるのではないでしょうか？
可能であれば、どのような内容か、質問に追記していただきたいです。

[shirogohan]

QUERY関数とIMPORTRANGE関数を使用して、他スプレッドシートの記載内容を転記しています。まさにこれが原因でしょうか…！？

[k.a_teratail]

> QUERY関数とIMPORTRANGE関数を使用して
こちらでもQUERY、IMPORTRANGE関数を使用しましたが、xlsx形式で問題なく出力されました。

コードの実行方法について、どのような方法で実行していますか？
・Apps Scriptの画面から関数を選択し「実行」をクリック
・デプロイしたURLにアクセスしてから実行
・スプレッドシートにボタンを配置し、ボタンをクリックして実行
・その他（上記以外の場合、どのように実施しているか詳しく記載してください）

[shirogohan]

度々ご確認ありがとうございます（ ; ; ）それではSpreadSheetの内容は関係なさそうですね。

コードの実行は
・Apps Scriptの画面から関数を選択し「実行」をクリック
しています。

[k.a_teratail]

こちらと同じ実行方法なので、なので問題なさそうですね。

原因が不明なため
以下、3つのご確認と結果を教えていただきたいです。

■1つ目
コード内に、sleep処理が入ってないので、処理実行時に429エラーが発生するかと思います。
なので、「sheets.forEach」内のどこでも良いので

Utilities.sleep(10000);

を記載して、実行してみてください。

■2つ目
新たなスプレッドシートを作成し
作成したスプレッドシート専用の「Apps Script」でコードを記載し実施
※シートは1つのみでA1セルに何でも良いので、テキストを入力

■3つ目
どのシートが問題なのか不明なため、シート1つ1つを指定して実施してみる

const sheets = [ss.getSheetByName("シート名")];

[shirogohan]

一度全てコード打ち直したところ、無事にエクセル出力できました。誤字があったのかもしれません。ご丁寧に教えてくださり、本当に感謝しています。ありがとうございました。