GASを用いたエクセルへの入力

Question

google app scriptとJSの練習で、スプレッドシートに

<完成形>

このように、forによって入力したいのですが、3時間ほどハマっています。

かなり初歩的だとは思うのですが、どのようなコード・考えで入力すべきでしょうか。

現状の未完成コードは以下になります。

function myFunction() {
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadSheet.getActiveSheet()
   // 変数iが4になるまで1を足し続けよ
  for (let i= 1; i <= 4; i++) {
    // 変数jが4になるまで1を足し続けよ
    for (let j= 1; j <= 4; j++) {
      let range = sheet.getRange(i ,j)
      range.setValue("A"+ j)
    }
  }

よろしくお願いいたします。

Answer 1

完成系の絵があるのはとてもいいですね。
分かりやすいです。

もう一点、今起こっている上手く行っていない事も伝えるともっと良い質問になったかと思います。

つまり、BCD列に想定した値が入らなかったのが問題点な訳ですね。
とりあえず、質問者さんは練習とのことなんで既存コードに手を入れた方がいいのでしょう。

ポイントは1, 2, 3, 4をA, B, C, Dに置き換えられればクリアなわけでその方法は、
errormaker74さんの配列番号と文字を紐づける方法がシンプルで分かりやすく、ほかの言語でも活用できる方法と思います。

その他の方法としては、GAS専用ですがこんなのがあります。

Range.getA1Notation()
https://developers.google.com/apps-script/reference/spreadsheet/range

let cellName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1 ,1).getA1Notation();
Logger.log(cellName); // 'A1'と出力する

どう考えるかの観点では私の回答は微妙ですが
まずはそういう機能が元から用意されているかどうかを調べ、あったらそれを使おう
という感じです。（無かったら自力実装ですがｗ）

頑張ってください。

Comments

[sarukawa]

ありがとうございます。今回はforの練習をしたく、GAS固有のメソッドは使いませんでした。
質問の仕方も難しいですね。また、よろしくお願い致します。

[sarukawa]

すみません、getRangeメソッドもGAS固有のものでした。

Answer 2

サンプルのコードを書いてみました。
ループ内で1セルずつ書き込むとおそらくリクエストのAPI制限がかかると思います。
必要なデータを作成してから1度で書き込むほうが良いでしょう。

function myFunction() {

  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getActiveSheet();

  // 行のデータ
  let rows = ["A","B","C","D"];

  // 書き込む列数
  let cols = 4

  // スプレッドシートに代入するためのデータ
  let data = [];
  for (let i=1;i<=cols;i++) {
    var row = [];
    rows.forEach(function(value){
      row.push(value.concat(i.toString()));
    })
    data.push(row)
  }

  // スプレッドシートに1,1のセル位置から範囲指定して代入
  sheet.getRange(1,1,rows.length,cols).setValues(data);

}

== 追記 ==

「列数は100まで最終的にZに100Zと入力したい」という点について以下に加筆します。
サンプルのコードを書いてみました。

function myFunction() {

  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadSheet.getActiveSheet();

  // 列名
  let cols = [
    "A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"
  ];

  // 行数
  let rows = 100;

  let data = []; 
  // 行数分だけループ 
  for (let i=1;i<=rows;i++) {
    var row = [];
    // 列数分だけループ
    cols.forEach(function(value){
      row.push(value.concat(i.toString()));
    })
    data.push(row)
  }

  // スプレッドシートに1,1のセル位置から範囲指定して代入
  sheet.getRange(1,1,rows,cols.length).setValues(data);

}

Comments

[sarukawa]

ありがとうございます。初めに配列と列数を準備し、data[]にpushする方法があったんですね。
今、行はZまで、列数は100まで最終的にZに100Zと入力したいのですが、書き込む列数を100に変更した場合では上手く行きません。完成形をイメージで追加しました。
rowsがA,B,C...Zまで26個あるかと思います。console.log(row)をdata.push(row)の後に入れて調べるとZ100までrowに入っているものの、実行するとエラーで、「The data has 100 but the range has 26.」と出てしまいます。行うことは根本的に変わらないとは思うのですが、数時間解決できない状況です。。助けていただけないでしょうか。よろしくお願い致します。

[errormaker74]

> 行はZまで、列数は100まで最終的にZに100Zと入力したい

回答のほうに追記で記載しました。

[sarukawa]

ありがとうございます。
同様にA-Zまでの配列を用意していたのですが、最後のgetRange, setValuesのところで理解が甘かったようです。
①sheet.getRange(1,1,rows.length,cols).setValues(data);
を、
②sheet.getRange(1,1,cols,rows.length,).setValues(data)
に変更、つまりrowsとcolsの順序を逆にすると思い通りの挙動になる点ですが、なぜ①だとエラーが起きるのかがわかりません。差し支えなければ、ご教授願えないでしょうか。宜しくお願い致します。

[errormaker74]

私が最初に書いたサンプルコードでは行と列を逆に考えてしまっていました。(4行4列なので気付きませんでした)
つまり、
・rowsの変数に入れているのは列名
・colsの変数に入れているのは行数
になっていました。

getRange()の引数には下記のドキュメントの通り、
getRange(row, column, numRows, numColumns)と渡さなければなりません。
・row：範囲の開始行インデックス
・column：範囲の開始列インデックス。
・numRows：範囲の行数
・numColumns：範囲の列数

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows,-numcolumns

そのため、numRowsの位置にはcolsを、numColumnsの位置にはrowsの要素数を渡さなけばなりません。

[sarukawa]

ありがとうございます！getRangeメソッド特有の引数の渡し方なのですね。
公式ドキュメントを読み込む重要性も分かりました。丁寧にご回答いただき、ありがとうございました。