質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.46%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

JavaScript

JavaScriptは、プログラミング言語のひとつです。ネットスケープコミュニケーションズで開発されました。 開発当初はLiveScriptと呼ばれていましたが、業務提携していたサン・マイクロシステムズが開発したJavaが脚光を浴びていたことから、JavaScriptと改名されました。 動きのあるWebページを作ることを目的に開発されたもので、主要なWebブラウザのほとんどに搭載されています。

Q&A

解決済

1回答

3841閲覧

jsからGASでスプレッドシートにデータを保存したい

bridgebook

総合スコア3

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

JavaScript

JavaScriptは、プログラミング言語のひとつです。ネットスケープコミュニケーションズで開発されました。 開発当初はLiveScriptと呼ばれていましたが、業務提携していたサン・マイクロシステムズが開発したJavaが脚光を浴びていたことから、JavaScriptと改名されました。 動きのあるWebページを作ることを目的に開発されたもので、主要なWebブラウザのほとんどに搭載されています。

1グッド

4クリップ

投稿2022/12/20 08:34

前提

ウェブページで投稿した内容をスプレッドシートに書き込む/読み出す仕組みを作っていますが、上手くいかないところがあり質問致します。

実現したいこと

・投稿内容すべてをjavascriptで一括でPOSTして、GASでスプレッドシートに書き込みたい。
・投稿内容すべてをスプレッドシートから一括でGETして、リストに表示したい。

発生している問題・エラーメッセージ

・fetchメソッドでPOSTする際に以下のエラーが出て、スプレッドシートに書き込みが出来ないようです。

127.0.0.1/:1 Access to fetch at 'https://script.google.com/******************/exec' from origin 'http://127.0.0.1:5500' has been blocked by CORS policy: Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled. script.js:308 POST https://script.google.com/******************/exec net::ERR_FAILED postToGAS @ script.js:308 script.js:313

該当のソースコード

javascript

1//GAS WebアプリのURL 2const END_POINT = 3 'https://script.google.com/******************/exec'; 4const sourceList =[ 5{source1: 'DD', source2: 'dd', done: false, id: '#source_table'}, 6{source1: 'CC', source2: 'cc', done: false, id: '#source_table'}, 7{source1: 'BB', source2: 'bb', done: false, id: '#source_table'}, 8{source1: 'AA', source2: 'aa', done: false, id: '#source_table'}, 9] 10 11//読み書きするスプレッドシート(タブ)の番号 12const SHEET_NO = 1; 13submit2.addEventListener('click', postToGAS, false); 14 15function postToGAS() { 16 17 //POST送信 18 const postparam = { 19 method: 'POST', 20 headers: { 'Content-Type': 'application/json' }, 21 body: JSON.stringify(sourceList), 22 }; 23 24 fetch(END_POINT, postparam) 25 .then((response) => { 26 console.log('成功'); 27 }) 28 .catch((error) => { 29 console.log('失敗'); 30 }); 31 32 33

GAS

1 2const sheet_id = '*******************************' 3const sheet_name = 'シート1' 4const sheet = SpreadsheetApp.openById(sheet_id).getSheetByName(sheet_name); 5 6function doPost(e) { 7 const reqParam = e.parameter; 8 const sheetNo = parseInt(reqParam.sheetNo) || 1; 9 sheet = ss.getSheets()[sheetNo - 1]; 10 var data = JSON.parse(reqParam.data); 11 postJsonToSpreadSheet(data); 12 return ContentService.createTextOutput(JSON.stringify({ result: "post done" })) 13} 14function postJsonToSpreadSheet(arrObj) { 15 //受け付けるJSONは、[{key1:data1, key2:data2,....},...]、オブジェクトが配列になっている形式 16 //オブジェクトのキーがスプレッドシートの項目名として1行目に入力される 17 sheet.clear(); 18 //タイトル行を書き込み 19 const keys = [Object.keys(arrObj[0])];//setValuesには必ず2次元配列を渡すので [ ] で囲んで2次元配列にする 20 sheet.getRange(1, 1, 1, keys[0].length).setValues(keys);//項目名を書き込み 21 //オブジェクトからデータ書き込み用の2次元配列を作成 22 const arrToWrite = arrObj.map((obj) => { 23 const arr = []; 24 for (const key of keys[0]) { arr.push(obj[key]); } 25 return arr; 26 }); 27 const lastColumn = arrToWrite[0].length; //1個め配列の長さ=カラムの数を取得する 28 const lastRow = arrToWrite.length; //行の数を取得する 29 sheet.getRange(2, 1, lastRow, lastColumn).setValues(arrToWrite); 30} 31 32

試したこと

・postparamに「mode: 'no-cors'」を追加したところ、「成功」が表示されましたが、スプレッドシートに書き込まれませんでした。

yamanokuchi👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

「・投稿内容すべてをjavascriptで一括でPOSTして、GASでスプレッドシートに書き込みたい」について、先に動作する回答例を示して、後半で解説します。
(2番目の「・投稿内容すべてをスプレッドシートから一括でGETして、リストに表示したい」については具体的なコードが質問に一切示されておらず要件が全く不明であるため、割愛します)

<例>
クライアント側HTML sample.html
(コードの一部は、元質問のものを残しております)

html

1<!DOCTYPE html> 2<html lang="ja"> 3 4<head> 5 <meta charset="utf-8"> 6 <title>GAS/JS TEST by qnoir</title> 7 8<body> 9 <button type="button" id="submit2">submit</button> 10 <script> 11 //GAS WebアプリのURL 12 const END_POINT = 'https://script.google.com/******************/exec'; 13 14 //読み書きするスプレッドシート(タブ)の番号 15 const SHEET_NO = 1; 16 17 const sourceList = { 18 sheetNo: SHEET_NO, 19 data: [ // GAS側で data プロパティにアクセスしているため、 20 // クライアントから送るデータにも data プロパティが必要。 21 { source1: 'DD', source2: 'dd', done: false, id: '#source_table' }, 22 { source1: 'CC', source2: 'cc', done: false, id: '#source_table' }, 23 { source1: 'BB', source2: 'bb', done: false, id: '#source_table' }, 24 { source1: 'AA', source2: 'aa', done: false, id: '#source_table' }, 25 ] 26 }; 27 28 const submit2 = document.getElementById("submit2"); 29 submit2.addEventListener('click', postToGAS, false); 30 31 function postToGAS() { 32 const postparam = { 33 method: 'POST', 34 body: JSON.stringify(sourceList), 35 }; 36 37 fetch(END_POINT, postparam).then(response => { 38 console.log(response.status); 39 return response.json()}; 40 ).then((data) => { 41 console.log(data.result); 42 }).catch(err=>{ 43 console.log("Error!"); 44 }); 45 </script> 46</body> 47</html>

GAS側のスクリプト code.gs
(コメントの大部分は、元質問のものを残しております)

js

1const sheet_id = '*******************************'; 2const sheet_name = 'シート1'; 3const ss = SpreadsheetApp.openById(sheet_id); 4 5function doPost(e) { 6 try { 7 const res = doPostProxy(e); 8 return res; 9 } catch (err) { 10 return ContentService.createTextOutput(JSON.stringify({ result: err.stack})); 11 } 12} 13function doPostProxy(e) { 14 const reqParam = JSON.parse(e.postData.getDataAsString()); 15 const sheetNo = parseInt(reqParam.sheetNo) || 1; 16 // シートの取得 17 const targetSheet = ss.getSheets()[sheetNo - 1]; 18 // data プロパティの取得 19 var data = reqParam.data; 20 postJsonToSpreadSheet(data, targetSheet); 21 return ContentService.createTextOutput(JSON.stringify({ result: "post done" })); 22} 23 24function postJsonToSpreadSheet(arrObj, targetSheet) { 25 //受け付けるJSONは、[{key1:data1, key2:data2,....},...]、オブジェクトが配列になっている形式 26 //オブジェクトのキーがスプレッドシートの項目名として1行目に入力される 27 targetSheet.clear(); 28 //タイトル行を書き込み 29 const keys = [Object.keys(arrObj[0])];//setValuesには必ず2次元配列を渡すので [ ] で囲んで2次元配列にする 30 targetSheet.getRange(1, 1, 1, keys[0].length).setValues(keys);//項目名を書き込み 31 //オブジェクトからデータ書き込み用の2次元配列を作成 32 const arrToWrite = arrObj.map((obj) => { 33 const arr = []; 34 for (const key of keys[0]) { arr.push(obj[key]); } 35 return arr; 36 }); 37 const lastColumn = arrToWrite[0].length; //1個め配列の長さ=カラムの数を取得する 38 const lastRow = arrToWrite.length; //行の数を取得する 39 targetSheet.getRange(2, 1, lastRow, lastColumn).setValues(arrToWrite); 40}

説明

CORS周りについて

・fetchメソッドでPOSTする際に以下のエラーが出て、スプレッドシートに書き込みが出来ないようです。

元コードのように、'content-type': 'application/json' を指定すると、プリフライト・リクエストになります。プリフライト・リクエストを処理するには、サーバーが対応している必要がありますが、GAS は 非対応です。
このため (「mode: 'no-cors'」 がない状態で 'content-type': 'application/json' を指定したリクエストを GAS に送った場合) GAS との通信は CORS policy によりブロックされます。

・postparamに「mode: 'no-cors'」を追加したところ、「成功」が表示されましたが、スプレッドシートに書き込まれませんでした。

「mode: 'no-cors'」に設定すると、たしかに CORS エラーは発生しませんが、レスポンスは opaque となり、GAS から JSON を返しても無視されて、空っぽのデータが返ってきます。

つまり GAS 内部でエラーがあろうとなかろうと、no-cors にしていると常に空っぽのレスポンスがポツンと返ってくるわけで(エラー扱いにならないので fetch の .catch にもつかまりません)、上のコードで「成功」と表示されたからと言ってGAS側の処理が正常に完了したとは限りません。
(実際、元コードは GAS 側のコードにいくつかエラーがあり、スプレッドシートへ正常書き込みが行われる前にエラーで終了しています。)

結論として、GAS から post後の正常メッセージ/エラーメッセージを、内容がわかる形で返そうとするならば「mode: 'no-cors'」は使えません。

では、「mode: 'no-cors'」無しで通したい場合どうすればいいか?
→ そもそもcontent-type': 'application/json' を指定するとプリフライト・リクエストになってしまうことが問題でした。
したがって、リクエストがいわゆる単純リクエストになるように content-type': 'application/json' を削除します。

GASの修正

① doPostでの postリクエスト内のペイロードの取得方法。

doPost 関数で受け取った Postリクエスト内のペイロード(データ)は、
e.parameter ではなく、
e.postData で取得します。

さらに、本件の場合、クライアントから JSONデータが送られてくる形なので、データを文字列化したうえで parseします。

js

1 const reqParam = JSON.parse(e.postData.getDataAsString());
② クライアントから送るデータ(sourceList)に dataプロパティ がないにもかかわらず、GAS側 で data プロパティを読み取ろうとしている。

js

1 var data = JSON.parse(reqParam.data);

この部分ですが、クライアントから送られてくる JSON には data プロパティがないにもかかわらず、data を読み取ろうとしており、結果 reqParam.data は undefined になってしまいます。
→ sourceList のデータに data プロパティを付けるか、sourceList はそのままにして「.data」を削除します。
あと、①で parse 済みであれば、この部分のJSON.parse() も不要になります。

③ GASからの戻り値の設定

mode:'no-cors'を設定していない場合で単純リクエストの場合:
GAS のWebアプリからの戻り値は

  • 正常終了の場合 → Access-Control-Allow-Origin * 付きでレスポンスが返ってくる
  • エラーが発生した場合 → Access-Control-Allow-Origin なしで返ってくる → CORS policy によりブロックされる

となります。

mode:'no-cors'を設定した場合:
GAS のWebアプリからの戻り値は、正常終了かエラーにかかわらず、opaque なレスポンスとなります。

したがって、GAS 内部で発生したエラー内容を捕捉したいのであれば、GAS 側で doPost 内の処理を丸ごと try ~ catch ブロックで囲み、例外処理の中でエラー内容を(正常に)返すようなギミックを使います。(同時にエラー内容をログシートに出力するようにしてもよいでしょう)

js

1// Postリクエスト処理中のエラーをトラップするため try ~ catch で囲む。 2function doPost(e) { 3 try { 4 const res = doPostProxy(e); 5 return res; 6 } catch (err) { 7 // エラーのスタックを JSON 形式で返す。 8 return ContentService.createTextOutput(JSON.stringify({ result: err.stack})); 9 } 10} 11 12// doPost 処理を代理で行う 13function doPostProxy(e) { 14 // もともとの doPost の処理 15 // .... 16 17 // 正常終了のメッセージを返す 18 return ContentService.createTextOutput(JSON.stringify({ result: "post done"})); 19}
④ 未定義の変数

ss という変数が内部で使われていますがどこにも定義されていません。おそらくスプレッドシートのことだろうと推測したので、とりあえずスプレッドシートオブジェクトを格納したグローバル変数として定義しています。

⑤ グローバル変数の(意図しない?)上書き

グローバル変数で定義している sheet という変数が doPost内で上書きされており、このために期待する動作になっていません。
sheet はローカル変数とし、postJsonToSpreadSheet に引数として渡すようにすればよいでしょう。(修正後コード中の変数「targetSheet」参照)


あまり推奨しませんが、'content-type': 'application/json', かつ mode: 'no-cors' を指定した場合、
GAS 側に一切バグがなければ、一応スプレッドシートに正常に書き込まれます。
ただし上述の通り、仮にその後GASのコードを修正してGAS内部でエラーが発生したとしても、クライアント側ではエラー内容がわからないため、try~catch で囲んでログシートにエラー内容を記録する / スクリプトをGCPと結びつけてGoogleコンソールでログを確認する等しない限り、デバッグに苦労することになるでしょう。

投稿2022/12/21 12:19

編集2023/01/05 13:14
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

bridgebook

2022/12/22 07:30

ご丁寧な回答ありがとうございました。記載の方法で解決しました。勉強していきます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.46%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問