Go to Qiita Advent Calendar 2024 Top
LoginSignup
takahirokomori0512
@takahirokomori0512

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

GAS 複数シートのデータを取得

Q&A

Closed

GAS,javascript,初心者

解決したいこと

プログラミング3ヵ月目のものです。
GASを使って、FormとSpreadSheetを組み合わせた案件整理シートをつくっています。
シート数が定まっていないシートの特定した範囲を取得して、formula関数で集計シートを作成しようとしましたが、上手くいきません。
どうかお力をお貸しください。

発生している問題

let spreadsheet = SpreadsheetApp.openById("");
let originalSheet = spreadsheet.getSheetByName("");

const FORM = {
  ASSIGNER: 0,
  DATE: 1,
  OWNER: 2,
  BAGGAGE: 3,
  BRANCH: 4,
  QUANTITY: 5,
  START_TIME: 6,
  ITEM: 7,
  ISSUE: 8,
  ACTION: 9
}

function onFormSubmit(e) {
  let itemResponses = e.response.getItemResponses();

  let Responses = [];

  for (let i = 0; i < 10; i++) {
    Responses.push(itemResponses[i].getResponse())
  }
  const writeResponses = [
  [
    Responses[FORM.ASSIGNER],
    Responses[FORM.DATE],
    Responses[FORM.BAGGAGE],
    Responses[FORM.BRANCH],
    Responses[FORM.QUANTITY],
    Responses[FORM.START_TIME],
    Responses[FORM.ITEM],
    Responses[FORM.ISSUE],
    Responses[FORM.ACTION],
  ]
];
  let sheet = spreadsheet.getSheetByName(Responses[2]);

  if (sheet === null) {
    let sheet = originalSheet.copyTo(spreadsheet).setName(Responses[2])
};

  const responseRange = sheet.getRange(3, 1, 1, 9); // first line
  sheet.insertRowBefore(3);
  responseRange.setBorder(true, true, true, true, true, true, "BLACK", SpreadsheetApp.BorderStyle.SOLID);
  responseRange.setValues(writeResponses);
  sheet.activate();
  spreadsheet.moveActiveSheet(3);
};


let tallySheet = spreadsheet.getSheetByName("集計シート");
let sheets = spreadsheet.getSheets();

for (let i = 2; i < sheets.length; i++){
let sheetName = sheets[i].getName();
let setformula = tallySheet.getRange().
setFormula()
};

該当するソースコード

ソースコードを入力

例）

自分で試したこと

ここに問題・エラーに対して試したことを記載してください。

0

3Answer

nak435
@nak435

formula関数で集計シートを作成しようとしましたが、上手くいきません。

具体的な現象を書いてください。
式も書いてください。スクショを貼るだけでも違います。

0Like
takahirokomori0512
@takahirokomori0512

フォームの回答（計10問）の回答送信をトリガーに、回答の3問目OWNERをシート名にして作成もしくは上書き、3問目を除く、1～10問目を当該シートのA3:A10にセットする。
それぞれの最新の回答＝各シートのA3:A10をtallysheetに集計しようとした際に、下記のようなエラーがでて進めない状況です。
先ほどの質問の式と少し変えてイベントオブジェクト内に集計シートへの書き込みを入れてみましたが、変わらずエラーが出てしまいます。
情報が多く申し訳ないですが、ご教示いただけますと幸いです。

式
let spreadsheet = SpreadsheetApp.openById("18-MkvOsT7I8faiBGpee9VZkehXAixRMJrd7C8dEg-4I");
let originalSheet = spreadsheet.getSheetByName("シート1");

const FORM = {
ASSIGNER: 0,
DATE: 1,
OWNER: 2,
BAGGAGE: 3,
BRANCH: 4,
QUANTITY: 5,
START_TIME: 6,
ITEM: 7,
ISSUE: 8,
ACTION: 9
};

function onFormSubmit(e) {
let itemResponses = e.response.getItemResponses();

let Responses = [];

for (let i = 0; i < 10; i++) {
Responses.push(itemResponses[i].getResponse())
};
const writeResponses = [
[
Responses[FORM.ASSIGNER],
Responses[FORM.DATE],
Responses[FORM.BAGGAGE],
Responses[FORM.BRANCH],
Responses[FORM.QUANTITY],
Responses[FORM.START_TIME],
Responses[FORM.ITEM],
Responses[FORM.ISSUE],
Responses[FORM.ACTION],
]
];
let sheet = spreadsheet.getSheetByName(Responses[2]);

if (sheet === null) {
let sheet = originalSheet.copyTo(spreadsheet).setName(Responses[2])
};

const responseRange = sheet.getRange(3, 1, 1, 9); // first line
sheet.insertRowBefore(3);
responseRange.setBorder(true, true, true, true, true, true, "BLACK", SpreadsheetApp.BorderStyle.SOLID);
responseRange.setValues(writeResponses);
sheet.activate();
spreadsheet.moveActiveSheet(3);
//以下、エラーが発生する式になります。
let tallySheet = spreadsheet.getSheetByName("集計シート");
let sheetName = sheet.getName();
tallySheet.getRange(tallySheet.getLastRow()+1, 1, tallySheet.getLastRow(), 9).setFormulas(
["='sheetName()'!A3"],
["='sheetName()'!B3"],
["='sheetName()'!C3"],
["='sheetName()'!D3"],
["='sheetName()'!E3"],
["='sheetName()'!F3"],
["='sheetName()'!G3"],
["='sheetName()'!H3"],
)
};

エラー内容
TypeError: Cannot read properties of null (reading 'getRange')
at onFormSubmit(コード:53:14)

スクリーンショット 2024-03-31 114323.png
スクリーンショット 2024-03-31 114311.png
スクリーンショット 2024-03-31 114334.png
スクリーンショット 2024-03-31 114344.png
スクリーンショット 2024-03-31 114355.png
スクリーンショット 2024-03-31 114408.png

0Like

Comments

  1. @nak435

    3問目を除く、1～10問目を当該シートのA3:A10にセットする。

    A3:A10だと、8セルしかありませんが、9セル必要では？

    A3:J3の間違いですかね？

    コードとスクショは、これから見てみます。

  2. @nak435

    各シートのA3:A10をtallysheetに集計しようとした際に、下記のようなエラーがでて進めない状況です。

    各シートのスクショを見てみましが、tallysheetに"集計"していませんよね？
    A〜Dの各シートのA3:J3を、tallysheetにコピーしたように見えます。
    これがGASで行った"集計"でしょうか？

    コードは、これから見てみます。

  3. @nak435

    エラー内容
    TypeError: Cannot read properties of null (reading 'getRange')
    at onFormSubmit(コード:53:14)

    ↓この行がエラーですか？

        //以下、エラーが発生する式になります。
    let tallySheet = spreadsheet.getSheetByName("集計シート");

    "集計シート"という名前のシートが無いからでは？

  4. @takahirokomori0512

    Questioner

    ご対応ありがとうございます。
    ・tallySheetは集計ではなく、各OWNERのシートの最新データ(A3:A10)を参照するイメージでした。

    ・"集計シート"という名前は、ご質問するにあたり、"tallySheet"に変更したため、エラーの原因ではないと思います。

    わかりづらくすみませんが、引き続きよろしくお願いいたします。

  5. @nak435

    ↓以下でどうでしょうか？

      //以下、エラーが発生する式になります。
  let tallySheet = spreadsheet.getSheetByName("tallySheet");
  let sheetName = sheet.getName();
  tallySheet.getRange(tallySheet.getLastRow()+1, 2).setFormula('=ARRAYFORMULA(' + sheetName + '!A3:I3)');

  6. @takahirokomori0512

    Questioner

    @nak435様
    出来ました。行き詰まっていたので、大変助かりました。
    誠にありがとうございました！！

  7. @nak435

    解決したのであれば、当Q&Aをクローズしてください。

Your answer might help someone💌

LoginSign Up