見出し画像

【GAS】一行目に数式を入れてオートフィルする

結論

// 出力用のシートを取得しておく
const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet_b = activeSheet.getSheetByName("シートB")

// 入力値を受け取る
  let start_num = Number(Browser.inputBox("開始番号を入力してください"));
  if(start_num === "cancel"){return}
  let end_num = Number(Browser.inputBox("終了番号を入力してください"));
  if(end_num === "cancel"){return}  

// 必要な数式
  let fx_dic = {
    "A" : "='シートA'!An",
    "B" : "='シートA'!Cn",
    "C" : "=B1*1.1"
  }

  // 仮置き数字を実数に置換し、配列に格納し直し
  let fx_arr = []
  for(let key in fx_dic){
    let fx = fx_dic[key]
    fx_arr.push(fx.replace("n", start_num + 1))  // シートAにはヘッダがあるため+1する
  }
 
  // 出力シート一行目に元になる数式を入れる
  const source_range = sheet_b.getRange("A1:C1");
  source_range.setValues([fx_arr]);   // setValuesには二次元配列を渡す必要がある
  
  // オートフィルする
  const output_count = end_num - start_num + 1;
  const destination_address = "A1:C" + output_count
  const destination = price_sheet.getRange(destination_address);
  source_range.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

ちょっと手続き型手続き型しててかっこ悪いけど、とりあえず動くやつ。

やりたいこと

シートAに色々な情報が集約してある。
その中から必要な列×行だけをシートBに転記して、シートBをCSVとして保存したい。(※今回はCSVをローカルに保存したいので、CSV出力処理はSpredSheet側の機能を使い、GASでは触れない)

イメージとしてはこんな感じにしたいわけです

//シートA
管理番号 | 商品名 | 価格 | 商品備考 | 入荷日 | 出荷日 | あれや | これや
---------|-------|------|---------|--------|-------|-------|-------
1        | itemA | 1000 | hoge... |00/00/00|00/00/00| ...  |  ...
2        | itemB | 2000 | huga... |00/00/00|00/00/00| ...  |  ...
3        | itemC | 3000 |  nya... |00/00/00|00/00/00| ...  |  ...
//シートB
2 | 2000 | 2200
3 | 3000 | 3300 

転記したい行番号の最初と最後を受け取る

  let start_num = Number(Browser.inputBox("開始番号を入力してください"));
  if(start_num === "cancel"){return}
  let end_num = Number(Browser.inputBox("終了番号を入力してください"));
  if(end_num === "cancel"){return}  

今後計算とかに使うのでNumber()を噛ませて数値型にキャストして受け取ると後が楽です。
また、キャンセルが押された場合そこで処理を終了するようにしておかないとキャンセル押したのに処理が続いてかっこ悪いです。

数式を用意

// 必要な数式
  let fx_dic = {
    "A" : "='シートA'!An",
    "B" : "='シートA'!Cn",
    "C" : "=B1*1.1"
  }

  // 仮置き数字を実数に置換し、配列に格納し直し
  let fx_arr = []
  for(let key in fx_dic){
    let fx = fx_dic[key]
    fx_arr.push(fx.replace("n", start_num + 1))  // シートAにはヘッダがあるため+1する
  }

各セルに入れたい数式をべたーっと書いて用意します。
このとき、必ずしもシートAの1行目から拾いたいとは限らないので、nで仮置きしておいて後から置換します。
数字を置換するとき、シートAにはヘッダ行があるため、管理番号1番が2行目に存在します。なのでstart_numに+1しています。
また、C列に入れたい数式はシートBのB列を参照するので、常に1から始まりにしないと参照がおかしくなるため、nをおかずに直接1を入力しています。
この辺の細部はシートの実態に合わせて調整してください。

もっと複雑な数式を入れたくて、一つの数式にnが複数回登場する感じになった場合はこちらの記事参照。


出力シート一行目に、元になる数式を入れる

  const source_range = sheet_b.getRange("A1:C1");
  source_range.setValues([fx_arr]);   // setValuesには二次元配列を渡す必要がある

数式を入れたい範囲を指定してRangeオブジェクトを取得し、RangeオブジェクトのsetValues()メソッドを使って数式を入れます。
このとき、setValues()には二次元配列を渡す必要があります。取得した範囲が1行しかなくても二次元配列が必要なので、fx_arrは既に配列になっていますが、それをさらに配列の中に突っ込む形で差し上げます。

オートフィルする

  const output_count = end_num - start_num + 1;
  const destination_address = "A1:C" + output_count
  const destination = price_sheet.getRange(destination_address);
  source_range.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

今回、出力行が1から始まるとは限らないので、必要な行数を計算して、オートフィルする範囲(destination)を指定します。
で、範囲を決めたらRangeを取得しておき、数式を入れる為に取得した一行の方のRangeオブジェクトのautoFillメソッドにdestinationを差し上げてオートフィルします。

ちなみに、fx_arrを生成する段階で、出力したい行数分forで回して数式全部作っちゃってからsetValuesする方法もあるんですが、forで回すよりオートフィル使う方が処理が早いっぽいので、最近はオートフィルする方法を使っています。お好みで。


この記事が気に入ったらサポートをしてみませんか?