Go to Qiita Advent Calendar 2024 Top
LoginSignup
21
13

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Google Apps Scriptを実例交えて基礎からざっくり学ぶAdvent Calendar 2017

Day 2
@rf_p(ryu fukuda)in株式会社エイチームライフデザイン

【Google Apps Script】その2　スプレッドシートのデータと連携する

Last updated at Posted at 2017-12-11

この記事はGoogle Apps Scriptを実例交えて基礎からざっくり学ぶ Advent Calendar 2017　2日目の記事です。

本アドベントカレンダーは@rt_pの個人プロジェクトですが、筆者はAteam Brides Inc. Advent Calendar 2017にも参加しています。そちらでも出張版記事を書いているので、覗いていただけると嬉しいです。

はじめに

今回はGASを使い、スプレッドシートにデータをセットしたり、スプレッドシートからデータを取ってくる方法を紹介します。

setValues.PNG

1．スプレッドシートにデータをセット

1日目のHello, world!記事の復習になります。
まずスプレッドシートを新規作成しスクリプトエディタを開いてください。
やり方がわからない場合は1日目のHello, world!記事をご参照ください。

set_data.gs
function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue('カレーライス');
}

ちなみにgetRange()でセル番地を指定していますが、下記コードでも全く同じ結果が得られます。

set_data2.gs
function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue('カレーライス');
}

getRange(row, column)

第1引数が行（上から何番目の行）、第2引数が列（左から何番目の列）を示しています。

Excel、スプレッドシート的な参照方法だと前者('A1')の指定方法が分かりやすく、GASの中で変数として行や列を指定する際は後者(1, 1)の指定方法が分かりやすそうですね。

setValue.PNG

2．スプレッドシートからデータを取得

今後は逆に、スプレッドシートに入力されているデータをGAS側で扱えるように取得してみましょう。
まずはスプレッドシートのA1セルに適当な値を入力します。

getValue1.PNG

次にGASで以下コードを入力し実行します。

get_data.gs
function myFunction() {
  var value = SpreadsheetApp.getActiveSheet().getRange('A1').getValue();
  Logger.log(value);
}

getValue()

実行しても何も起きないように見えますが、Logger.log()でログに吐き出しているのでそちらを確認してみましょう。
Ctrl+Enterでログを表示します。

getValue2.PNG

3．複数セルを対象としたセット

今後は、複数のセルに値を入れてみましょう。
1行目に、1, 2, 3, …, 10
2行目に、11, 12, 13, …, 20
…
10行目に、91, 92, 93, …, 100
とデータを入れたい時にどうすれば良いでしょうか？
（まぁこの程度の操作であれば、スプレッドシート上でドラッグ操作した方が早いかもしれないですが:sweat_smile:）

勘の良い方であれば、for文を駆使すればイケそうだと気付きそうですね。

set_values.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < 10; i++) {
    for (var j = 0; j < 10; j++) {
      var row = i + 1;
      var col = j + 1;
      var value = i * 10 + j + 1;
      sheet.getRange(row, col).setValue(value);
    }
  }
}

setValues.PNG

イイ感じに動きましたね！
ただ、ここで問題が1個あります。
実は**getRange()やらsetValue()等を実行する度に、GASからスプレッドシートのAPI呼び出しが発生します。**
当然その分処理に時間はかかるので、できることなら1回でバッと取ってきて1回でサクッとセットしたいですね。

その場合はgetRange()で複数セルを範囲として取得し、setValues()で二次元配列を渡すことで一度にセットができます。

set_values2.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = [];
  for (var i = 0; i < 10; i++) {
    values[i] = [];
    for (var j = 0; j < 10; j++) {
      var value = i * 10 + j + 1;
      values[i][j] = value;
    }
  }
  Logger.log(values);
  sheet.getRange('A1:J10').setValues(values);
}

setValues(values)

どんな配列をセットしているのか分かるようにログに吐いています。
Ctrl+Enterで確認してみてください。

ちなみにログだと分かりづらいかもしれないので、セットしている配列はどんなものかは以下をご確認ください。

set_values_sample.gs
  var values = [
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15, 16, 17, 18, 19, 20],
    [21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
    [31, 32, 33, 34, 35, 36, 37, 38, 39, 40],
    [41, 42, 43, 44, 45, 46, 47, 48, 49, 50],
    [51, 52, 53, 54, 55, 56, 57, 58, 59, 60],
    [61, 62, 63, 64, 65, 66, 67, 68, 69, 70],
    [71, 72, 73, 74, 75, 76, 77, 78, 79, 80],
    [81, 82, 83, 84, 85, 86, 87, 88, 89, 90],
    [91, 92, 93, 94, 95, 96, 97, 98, 99, 100]
  ];

ちなみに以下コードでも同じ結果が得られます。

set_values2.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = [];
  for (var i = 0; i < 10; i++) {
    values[i] = [];
    for (var j = 0; j < 10; j++) {
      var value = i * 10 + j + 1;
      values[i][j] = value;
    }
  }
  Logger.log(values);
  sheet.getRange(1, 1, 10, 10).setValues(values); // この行が違う
}

getRange()の指定方法が変わりました。

getRange(row, column, numRows, numColumns)

第1引数が行番号、第2引数が列番号、第3引数が行数、第4引数が列数になります。
getRange(1, 1, 10, 10)だと1, 1つまりA1から開始し、縦に10行、横に10列選択しています。

例えばC4:D9をこの指定方法で選択するならgetRange(4, 3, 6, 2)となります。
→C4セルは上から4行目、左から3番目、C4からD9は縦に6セル横に2セルの為

set_values_example.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(4, 3, 6, 2).setValue(1);
}

example.PNG

しれっとやっていますが、複数セルを範囲指定してsetValues()ではなくsetValue()を実行すると、範囲内全てのセルに同じ値をセットできます。

4．複数セルを対象とした取得

ここまで来るとほぼ分かるかもしれないですが、複数のセルから値を取るには
setValue()に対するsetValues()のように、getValue()に対する**getValues()**を使います。

getValues1.PNG

get_values.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange('A1:B3').getValues();
  Logger.log(values);
}

実行したらCtrl+Enterでログを確認。

getValues2.PNG

無事にデータを取得できました。

5．数式を取得する

さて、先程使ったサンプルの「カレーライス、ラーメン、チャーハン」の合計金額をSUM関数で集計してGASで取得してみると、どういう結果が返ってくるでしょうか？

getFormula1.PNG

getFormula2.PNG

1800.0という結果が返ってきました。
つまり、getValue()は数式ではなく計算結果を返します。

では、=sum('B1:B3')という数式を文字列として取得したい場合はどうすれば良いのでしょうか？

GASではgetFormula()を使います。

get_formula.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = sheet.getRange('B4').getFormula();
  Logger.log(formula);
}

getFormula()

実行してみると、=sum('B1:B3')が取得できていることが確認できます。

getFormula3.PNG

getFormulas()、setFormula()、setFormulas()等もありますが、考え方はvalue系と同じなので割愛します。

これでスプレッドシートの範囲を指定して、値をセット／取得することができるようになりました。
本日は以上です。

おわりに

getRange()、getValue()、setValue()の概念を理解できれば、今後紹介するGASによるスプレッドシート操作もすんなり理解できるはずです。

本アドベントカレンダーは、1日目～9日目あたりは基礎編でGASに関する基本的な操作を紹介していきます。
10日目以降は応用編ということで、実際に作ったアプリケーションとそれに関する技術の紹介になります。お楽しみに！

2018/01/15追記：
getActive()になっている箇所をgetActiveSheet()に修正しました。
@ocknamoさん、ご指摘ありがとうございました。

明日

【Google Apps Script】その3　外部スプレッドシートをDBとして利用する
となります。
お楽しみに！

前の記事
【Google Apps Script】その1　Hello, world!
次の記事
【Google Apps Script】その3　外部スプレッドシートをDBとして利用する

21
13
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
13

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?