前提

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

実現したいこと

・投稿内容すべてを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'」を追加したところ、「成功」が表示されましたが、スプレッドシートに書き込まれませんでした。

回答1件

ベストアンサー

「・投稿内容すべてを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