LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Saturday, December 9, 2017

setValuesを使う話



スプレッドシートに上の画像のように入力するには

[["aaa", "AAA"],["bbb", "BBB"]]

↑こういうデータをsetValues()に渡します


範囲をA1:B2に決め打ちでやるとこんな感じです


コード.gs
function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = [
    ["aaa", "AAA"],
    ["bbb", "BBB"]
  ]
  var range = sh.getRange("A1:B2");
  range.setValues(values);
}


範囲を決め打ちでやると
入力対象のデータが増えた時に行や列を書き直す必要があります

例えばこんなふうに増えると

[["aaa", "AAA", "トリプルエー"],["bbb", "BBB", "トリプルビー"], ["ccc", "CCC", "トリプルシー"]]

範囲をA1:C3のように書き換えないといけない
→var range = sh.getRange("A1:C3");

しかも既に値が入っている次の行から入力したい場合は
開始行と終了行を計算して書き直さないといけない


そこで
行や列の数が変わっても同じようにsetValuesで入力できるように
.getRangeで出てくる
getRange(row, column, numRows, numColumns)
を使って以下のようなコードを書きました


入力開始位置から何行・何列入力するのかを可変にしています

以下のコードのmyFunctionを実行すると
上部の画像のように値が入力されます


コード.gs
function myFunction() {
  var array = [
    ["aaa", "AAA"],
    ["bbb", "BBB"]
  ];
  set_values(array);
}

function set_values(array){
  var sh = SpreadsheetApp.getActiveSheet();
  var last_row = sh.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = array.length;
  var num_cols = array[0].length;
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(array); 
}
意訳
この処理は以下を実行する
[
[1行目の1列目のデータ, 2列目のデータ],
[2行目の1列目のデータ, 2列目のデータ]
]の形でarrayという名のデータを用意して
set_valuesに渡す


この処理は以下を実行する
開いているシートを取得して
データの入っている最終行を取得して
その次の行を入力を開始する行に設定して
入力を開始する列を設定する(例では1列目を指定)
入力する行数はarrayの長さから取得して
入力する列数はarrayの中の1行内のデータ数を取得して
入力を開始する行, 入力を開始する列, 入力する行数, 入力する列数で範囲を指定して
arrayのデータを入力する




試してみる


入力対象のデータを[["aaa", "AAA", "トリプルエー"],["bbb", "BBB", "トリプルビー"], ["ccc", "CCC", "トリプルシー"]]に変更して試してみます

変更箇所はvar arrayの中身を
var array = [
    ["aaa", "AAA", "トリプルエー"],
    ["bbb", "BBB", "トリプルビー"],
    ["ccc", "CCC", "トリプルシー"]
  ];
とするだけです


コード.gs
function myFunction() {
  var array = [
    ["aaa", "AAA", "トリプルエー"],
    ["bbb", "BBB", "トリプルビー"],
    ["ccc", "CCC", "トリプルシー"]
  ];
  set_values(array);
}

function set_values(array){
  var sh = SpreadsheetApp.getActiveSheet();
  var last_row = sh.getLastRow();
  var start_row = last_row + 1;
  var start_col = 1;
  var num_rows = array.length;
  var num_cols = array[0].length;
  var range = sh.getRange(start_row, start_col, num_rows, num_cols);
  range.setValues(array); 
}


myFunctionを実行すると
set_valuesにarrayのデータが渡されて
3行目から入力されます




参考
https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...