【GAS】スプレッドシートの値をスクリプトから操作するぞ - ポンコツエンジニアのごじゃっぺ開発日記。

この記事は GAS道場 Advent Calendar 2019 の3日目の記事です。 Google Apps Script(GAS)をこれから使おうという方向けのアドベントカレンダーになります。

本日はGASからスプレッドシートのセルの値を取得したり、値を入力したりする方法を紹介したいと思います。

スプレッドシートのデータ
データの取得
データの書き込み
スプレッドシート、シートを指定して取得
セルの装飾
さいごに

スプレッドシートのデータ

さて、今回使用するデータは以下のように、独自で作成したすき家のメニューと値段の表を使いたいと思います。

f:id:ponkotsu0605:20191126002408p:plain

	A	B
1	牛丼	350
2	ねぎキムチ牛丼	480
3	ねぎ玉牛丼	480
4	シャキっと和風オニサラ牛丼	500

このデータ群に対して操作したいと思います。

以下がドキュメントになります。

developers.google.com

データの取得

まずは、スプレッドシートに紐付いたスクリプトで考えてみます。スプレッドシートのヘッダーのメニューからツール→スクリプトエディタを開きます。

特定のセル1つのデータを取得する

まずは、1つのセルを指定して値を取得します。

function test1() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // (1,1)すなわちA1のセルを取得
  var data = sheet.getRange(1, 1).getValue();
  Logger.log(data);
}

実行結果

[19-11-26 00:37:34:487 JST] 牛丼

SpreadsheetApp.getActiveSpreadsheet(); で現在開いているスプレッドシートを指定します。 SpreadsheetApp.getActiveSheet(); で現在アクティブになっているシートを指定します。すなわち、現在開いているシートをこれだけで取得することができます。

そして、 var data = sheet.getRange(1, 1).getValue(); で(1,1)すなわちA1のセルの値を取得することができます。

指定した範囲のセルの値を取得する

次に、複数のセルの値を取得します。

function test2() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // (2,1)すなわちA2から2x2のサイズで取得
  var data = sheet.getRange(2,1,2,2).getValues();
  Logger.log(data)
}

実行結果

[19-11-26 00:38:57:613 JST] [[ねぎキムチ牛丼, 480.0], [ねぎ玉牛丼, 480.0]]

このように、2次元配列で指定したセルの値を取得できます。今回のスクリプトだと、 getRange(2,1,2,2).getValues(); ということで、A2のセルから2x2の複数のセルの値を取得するものになります。

データが入っているセルから取得する

意外と使える、データが入っているセルを自動で範囲指定してくれるものです。

function test3() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // データがあるセルを取得
  var data = sheet.getDataRange().getValues();
  Logger.log(data);
}

実行結果

[19-11-26 00:40:53:975 JST] [[牛丼, 350.0], [ねぎキムチ牛丼, 480.0], [ねぎ玉牛丼, 480.0], [シャキっと和風オニサラ牛丼, 500.0]]

このように、2次元配列ですべてのデータを取得することができました。

データの書き込み

先程はセルの値の取得を行いました。次にデータの書き込みをしてみたいと思います。

今回は行列の転置をjavascriptで書きつつ、それをセルに入力してみたいと思います。

function test4() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var data = sheet.getDataRange().getValues();
  data = transpose(data)
  Logger.log(data);
  sheet.clear();
  var rows = data.length;
  var cols = data[0].length;
  sheet.getRange(1,1,rows,cols).setValues(data);
  
}
function transpose(a) {
  return Object.keys(a[0]).map(function (c) {
    return a.map(function (r) {
      return r[c];
    });
  });
}

f:id:ponkotsu0605:20191126004343p:plain — 実行結果

transpose という関数は行列の転置をする関数になります。 (ネットからのコピペスクリプト) このように、スクリプトを実行するとスクショの画像のように、行と列が入れ替わっているのがわかります。 sheet.clear(); でシートを一度きれいに掃除をして、 sheet.getRange(1,1,rows,cols).setValues(data); でセルの範囲を指定しつつ、そこにデータを入力しています。このとき、データのサイズとgetRangeで指定するサイズは同じでないといけないので、配列の長さをlengthを使って取得しています。

スプレッドシート、シートを指定して取得

さて、ここまではスプレッドシートに紐付いたスクリプトの場合の、紐付いているスプレッドシートのデータの取得を紹介しました。では、複数のスプレッドシートのデータを同時に扱いたい場合はどうすればいいのでしょうか？

function test5() {
  // スプレッドシートのIDを指定して読み込む
  var spreadsheet = SpreadsheetApp.openById('1jWzpA5pZZHwjGb83u3gBqWM-tO9rdmUXyZJGMPkzbsY');
  // シート名を指定して取得
  var sheet = spreadsheet.getSheetByName('シート1');
  
  var data = sheet.getDataRange().getValues();
  Logger.log(data);
}

実行結果

[19-11-26 00:50:02:318 JST] [[牛丼, ねぎキムチ牛丼, ねぎ玉牛丼, シャキっと和風オニサラ牛丼], [350.0, 480.0, 480.0, 500.0]]

このように、スプレッドシートのIDやシート名を指定して開くことができます。ここでのスプレッドシートのIDとは、開いているスプレッドシートのURLの以下の[スプレッドシートID]というところがそのIDになります。 https://docs.google.com/spreadsheets/d/[スプレッドシートID]/edit#gid=0

これのIDを指定、そしてシート名を指定することで、そのシートのデータを取得することができます。なので、これを用いることで複数のスプレッドシート、複数のシートのデータを扱うことが可能となります。

セルの装飾

おまけですが、セルの背景色や文字色、罫線もつけてみましょう！

function test6() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // (1,1)すなわちA1のセルを取得
  sheet.getRange(1, 1).setBackground('#FFA500');
  sheet.getRange(1, 2).setFontColor('#00FF00');
  sheet.getRange(2, 2).setBorder(true, true, true, true, true, true)
}