【初心者向けGAS】スプレッドシートのセル範囲の値を二次元配列として取得して取り扱う方法

photo credit: classroomcamera DSC01821 via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

Google Apps Script初心者向けのお題として、名言Botの作り方をお伝えしています。

前回の記事はコチラ。

【初心者向けGAS】スクリプトプロパティを操作してそのデータを取り出す方法

初心者向けのシリーズとしてGoogle Apps Scriptで名言Botの作り方を解説しております。今回は、GASでPropertiesサービスを使ってスクリプトプロパティからデータを取り出す方法です。

APIトークンをプロパティストアから取得する方法についてお伝えしました。

もう、名言Botとしてはだいぶ完成なのですが、ちょっとだけまだやりたいことがあるので、お付き合いください。

まず、GASでは今後いたるところで格闘お付き合いすることになる、二次元配列についてです。

Google Apps Scriptでスプレッドシートのセル範囲の値を二次元配列として取得して取り扱う方法、行ってみましょう！

前回のおさらいと今回のお題
チャットワークへ送信する本文を構成する
配列とは
セル範囲の値を配列として取得する
getValuesメソッドを使ってメッセージ本文を構成する
まとめ
連載目次：超初心者向けGASでBotを作りながら基礎を学ぶ

前回のおさらいと今回のお題

まず、どんなBotを作っているか解説しますね。

以下のように、スプレッドシートに名言の数々がリストされています。

このスプレッドシートにバインドする形で、以下のスクリプトを作成し、チャットワークのマイチャットに名言をBotする時刻を指定した時限式のトリガーを仕込んでおります。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet(); 
  const lastRow = sheet.getLastRow();
  const token = PropertiesService.getScriptProperties().getProperty('CW_TOKEN');
  
  for(let i = 2; i <= lastRow; i++) {
    if(!sheet.getRange(i, 4).getValue()){ 
      const body = sheet.getRange(i, 1).getValue();
      sendMessage(token, body);
      sheet.getRange(i, 4).setValue(true);
      
      if(i >= lastRow) {
        sheet.getRange(2, 4, lastRow - 1).clearContent();
      }
      break;
    }
  }
}

function sendMessage(token, body){
  const cw = ChatWorkClient.factory({token: token});
  cw.sendMessageToMyChat(body);
}

このスクリプトが実行されると、以下のようにチャットワークに名言が送信されます。

というのが、このツールの仕組みです。

ですが、上記の送信内容…よく見ると、リアルに名言だけが送られていて、せっかく蓄積しているB列personや、C列infoが全く使われていませんね。

これは、もったいない…！

使いましょ。

チャットワークへ送信する本文を構成する

さて、上記のスクリプトの8行目、チャットワークに送信する本文を作って、変数bodyに格納する部分を、例えば以下のようにすれば、それぞれ送ることはできますね。

let body = '';
body += sheet.getRange(i, 1).getValue() + '\n';
body += sheet.getRange(i, 2).getValue() + '\n';
body += sheet.getRange(i, 3).getValue() + '\n';

実行すると…

ちょっと見づらいという問題もありますが、もう一つ別の問題があります。

スクリプトの中でgetRangeメソッドとかgetValueメソッドとかを何回も使っちゃってますよね。

これって実はあんまり良くなくって、以下記事にある通り、スプレッドシートへのアクセスの回数は、極力減らしたほうがいいんです。

Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法

Google Apps Scriptでスプレッドシートの操作をしていて実行速度が遅い！と感じたことがあると思います。今回はスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えします。

ということで、まずこの点について改良していきます。

配列とは

まず、配列について簡単に解説をしておきます。

配列とは、複数の値をまとめた集合のことで、以下のように表現されます。

[値1, 値2, …]

配列はそのままドンと変数に代入することもできます。

変数 = [値1, 値2, …]

そして、配列の値を取り出すには、以下のようにします。

配列[インデックス]

インデックスというのは、配列の要素に順番に付与された整数で、0から始まります。

「配列[0]」とすれば最初の値（要素と言います）を取り出すことができます。２番目の要素を取り出したければ「配列[1]」です。

セル範囲の値を配列として取得する

さて、前述のスクリプトでは、セル一つ一つを丁寧にgetRangeして、さらにgetValueしようとしたので、シートへのアクセス回数が増えちゃっていたわけです。

ですから、セルの範囲についていっぺんに、ガバっと値を配列に取得しちゃえばいいわけです。

それについては、以前お伝えした行数、列数を指定したgetRangeメソッドの使い方でセルの範囲を取得します。

書式はこうです。

Sheetオブジェクト.getRange(行番号, 列番号, 行数, 列数)

【初心者向けGAS】スプレッドシートのセル範囲を行数・列数を使って取得する

初心者向けにGoogle Apps ScriptでBotを作成する方法についてシリーズでお伝えしています。今回は、GASでスプレッドシートのセル範囲を行数・列数を使って取得する方法をお伝えしていきます。

それに加えて、こちらの以前の記事でチラリと解説をしたセル範囲を配列として取得するgetValuesメソッドを組み合わせます。

書き方はコチラです。

Rangeオブジェクト.getValues()

【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法

初心者向けにBotづくりを目指してGoogle Apps Scriptプログラミングの基礎をシリーズでお伝えしています。今回は、Rangeオブジェクト、つまりセルやセル範囲とその値を取得する方法です。

セル範囲の値をログ出力するスクリプト

例えば、以下のようにスクリプトを変更して、実行してみましょう。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet(); 
  const lastRow = sheet.getLastRow();
  const token = PropertiesService.getScriptProperties().getProperty('CW_TOKEN');
  
  for(let i = 2; i <= lastRow; i++) {
    if(!sheet.getRange(i, 4).getValue()){ 
        const values = sheet.getRange(i, 1, 1, 3).getValues();
        console.log(values);

//      let body = '';
//      body += sheet.getRange(i, 1).getValue() + '\n';
//      body += sheet.getRange(i, 2).getValue() + '\n';
//      body += sheet.getRange(i, 3).getValue() + '\n';
//
//      sendMessage(token, body);
//      sheet.getRange(i, 4).setValue(true);
      
      if(i >= lastRow) {
        sheet.getRange(2, 4, lastRow - 1).clearContent();
      }
      break;
    }
  }
}

11～17行目はコメントアウトしていますので、実際にチャットワークに送信が行われることはありません。

一方で8,9行目にgetValuesメソッドとconsole.logメソッドを入れて、取得したセル範囲の値をログ出力していますね。

行番号: i
列番号: 1
行数: 1
列数: 3

なので、i行目＆1列目を基点として、1行分×3列分をいっぺんに取得し、それをログ出力することになります。

セル範囲の値を格納した配列の構成

実行したら、valuesのログ出力はこうなりました。

[ [ ‘妻の機嫌が悪いときに、夫が責任を感じてはいけない。不機嫌でいるか上機嫌でいるかは、妻の問題。nその課題を勝手に背負うから苦しいのだ。’,
‘アルフレッド・アドラー’,
‘オーストリア出身の精神科医、心理学者、社会理論家 / 1870～1937’ ] ]

簡略化すると、以下のような構成になっています。

[[名言, 人物, 情報]]

角括弧が二重になっていますね。

これは、配列の中に一つの配列が入っている構造になっているのです。

このような状態の配列を二次元配列といいます。

二次元配列から要素を取り出す

角括弧にインデックスをつければ要素を取り出せますので、まず、先程のスクリプトの10行目を以下のように変更してみましょう。

console.log(values[0]);

二次元配列valuesのインデックス0の要素は、配列

[名言, 人物, 情報]

ですので、以下のようになります。

[ ‘妻の機嫌が悪いときに、夫が責任を感じてはいけない。不機嫌でいるか上機嫌でいるかは、妻の問題。nその課題を勝手に背負うから苦しいのだ。’,
‘アルフレッド・アドラー’,
‘オーストリア出身の精神科医、心理学者、社会理論家 / 1870～1937’ ]

さらに、そこからそれぞれの要素を取り出すには、values[0]が配列なわけですから、さらに角括弧でインデックスを指定して以下のようにすれば良いですね。

console.log(values[0][0]);
console.log(values[0][1]);
console.log(values[0][2]);

getValuesメソッドを使ってメッセージ本文を構成する

以上を踏まえて、スプレッドシートからセル範囲の値を二次元配列として取得、それをもとにメッセージ本文を構成するスクリプトを作成しました。

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet(); 
  const lastRow = sheet.getLastRow();
  const token = PropertiesService.getScriptProperties().getProperty('CW_TOKEN');
  
  for(let i = 2; i <= lastRow; i++) {
    if(!sheet.getRange(i, 4).getValue()){ 

        const values = sheet.getRange(i, 1, 1, 3).getValues();
        let body = '';
        body += values[0][0] + '\n'; //meigen
        body += values[0][1] + '\n'; //person
        body += values[0][2] + '\n'; //info
        sendMessage(token, body);
        sheet.getRange(i, 4).setValue(true);
      
      if(i >= lastRow) {
        sheet.getRange(2, 4, lastRow - 1).clearContent();
      }
      break;
    }
  }
}

まあ、実行しても結果は変わりませんけどね。

まとめ

以上、スプレッドシートのセル範囲の値を二次元配列として取得して取り扱う方法ついてお伝えしました。

配列、しかも二次元配列となると、ちょっと難しいように見えますが、スプレッドシートやGmailなど、GASでは二次元配列の扱う頻度はとても多く重要です。

なにせ、実行速度に直撃しますからね。

しばらくお付き合いすれば慣れてきますので、ぜひ前向きにお付き合いくださいませ。

なお、本当はもっと前段階でガバっと二次元配列としてシートのデータを取得して、処理をすることもできますので、機会があればチャレンジしてみてくださいね。

次回は、チャットワークの表示の体裁を整えていきたいと思います。

【初心者向けGAS】チャットワークのメッセージ記法でBot送信するメッセージを装飾する方法

初心者向けGoogle Apps Scriptのシリーズとしてチャットワークを使った名言Botを作る方法をお伝えしています。今回は、チャットワークの通知を装飾するメッセージ記法を使う方法をお伝えしています。

どうぞお楽しみに！

連載目次：超初心者向けGASでBotを作りながら基礎を学ぶ

Google Apps Script（GAS）をはじめるためのメリットは山程ありますが、何を作ったらいいの？と悩んでしまうこともありますよね。そんな時に、おすすめしたいのが「Bot」の作成です。このシリーズでは、超初心者向けにGASでBotを作る方法を題材としながら、GASプログラミングの一通りの流れと書き方について学んでいきます。