見出し画像
Photo by nirasaki

【GAS】スプレットシート連携_実践編その2（複数セルに出力する）

MARIE

おはようございます！MARIEです(｀・ω・´)ゞ

宿題の答え合わせ

前回出題した宿題は無事に完了しましたでしょうか？

宿題は以下でした。

集計結果をメールではなく、スプレットシートに書き出してみましょう。

では早速答えと解説に入っていきます。

【答えのプログラム】

function sendMailTravelDestination() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');

 const data = sheet.getDataRange().getValues();

 let hokkaidoCount = 0
 let okinawaCount = 0
 let osakaCount = 0

 for (let i = 1; i < data.length; i++) {
   const travel = data[i][1];
   if (travel === '北海道') {
     hokkaidoCount++;
   } else if (travel === '沖縄') {
     okinawaCount++;
   } else if (travel === '大阪') {
     osakaCount++;
   }
 }

 sheet.getRange('E1').setValue('集計結果');
 sheet.getRange('E2').setValue('北海道');
 sheet.getRange('E3').setValue('沖縄');
 sheet.getRange('E4').setValue('大阪');

 sheet.getRange('F2').setValue(hokkaidoCount);
 sheet.getRange('F3').setValue(okinawaCount);
 sheet.getRange('F4').setValue(osakaCount);

}

まず、カウントするところまでは同じですよね( ｀ー´)ノ
前回やったメールを送るをスプレットシートに集計結果を出力するに変えるだけなので、以下の部分がスプレットシートパターンに変更されました。

 sheet.getRange('E1').setValue('集計結果');
 sheet.getRange('E2').setValue('北海道');
 sheet.getRange('E3').setValue('沖縄');
 sheet.getRange('E4').setValue('大阪');

 sheet.getRange('F2').setValue(hokkaidoCount);
 sheet.getRange('F3').setValue(okinawaCount);
 sheet.getRange('F4').setValue(osakaCount);

もうわかりますよね？
getRangeで範囲を指定、setValueで値をセット。
つまり値をセットしたい範囲をgetRangeで決めて、セットしたい値をsetValueの後ろに置く。そんな形になっています。

画像1

結果はもちろんこうなり、無事にスプレットシートに出力することができました(ﾟ∀ﾟ)

複数の値をスプレットシートに入力してみよう

では今日は複数の値をスプレットシートに入力していくGASを学んでいきましょう。例えば、以下のような出力をするプログラムを作りたいとき。

画像2

こんなプログラムでも行けちゃいますが。

function setSomeValues() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = spreadsheet.getSheetByName('シート1');

 sheet.getRange('A1').setValue('あああ');
 sheet.getRange('B1').setValue('いいい');
 sheet.getRange('C1').setValue('ううう');
 sheet.getRange('A2').setValue('えええ');
 sheet.getRange('B2').setValue('おおお');
 sheet.getRange('C2').setValue('かかか');
}

実はこれには少し問題があります。

というのも、GASには制限があります。
その制限とは、実行時間の上限が6分までというものです。
これを超えるとプログラムが終了してしまいます。

その制限を踏まえもう一度先ほどのプログラムを見てみると、
「sheet.getRange('hogehoge').setValue('hogehoge');」のように、スプレットシートに対して1回1回アクセスをしている状態です。

スプレットシートとGASは住んでる家が違います。
ピンポーン！「A君いますか？じゃあ出てきてください」
ピンポーン♪「A君の妹ちゃんいますか？じゃあ出てきてください」
ピンポーン「Aくんのお兄ちゃんいますか？じゃあ出てきてください」
のように、1回1回GASがスプレットシートに家に、ピンポン（アクセス）を鳴らしている状態です。

これだとすごい効率が悪いし、やりたいことが時間制限によってできなくなってしまう可能性も出てきます。

複数の値を出力する良い方法とは

ではどうするのか？
実は一度に複数のセルに書き出す方法がGASにはあります。

function setSomeValues() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 
 const output = [
   ['あああ','いいい','ううう'],
   ['えええ','おおお','かかか']
 ];
 
 sheet.getRange('A1:C2').setValues(output);
}

出力したいデータを二次元配列で表現する。

 const output = [
   ['あああ','いいい','ううう'],
   ['えええ','おおお','かかか']
 ];

出力範囲を正確に指定し、「setValuesに二次元配列を渡す」のです。

sheet.getRange('A1:C2').setValues(output);

ポイント！データ数は揃えるが必須！

例えば、2行目のC列は空欄にしたい場合。

画像3

プログラムはこう書きます。（['えええ', 'おおお', '']）

function setSomeValues() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');

 const output = [
   ['あああ', 'いいい', 'ううう'],
   ['えええ', 'おおお', '']
 ];

 sheet.getRange('A1:C2').setValues(output);
}

つまり！セルに書き出したいデータは、初めから二次元配列の形で用意をしておくと処理がめっちゃ早くなり楽です( ｀ー´)ノ

スプレットシートの末尾に追加をしてみよう

で実際に業務に応用していく中で、データを下に追加していきたい。そんなことってきっとこれからちょいちょい発生してくるかもしれません。

そんな時のプログラムはこうです。

function pasteToLastRow() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');

 const output = [
   ['AAA', 'BBB', 'CCC'],
   ['DDD', 'EEE', 'FFF']
 ];

 // シートに存在する一番最後の行数を取得できる
 const lastRow = sheet.getLastRow();

 const fromRow = lastRow + 1;             // 最終行の次の行から
 const toRow = lastRow + output.length; // 最終行＋貼り付けたいデータの行数

 // 範囲を指定して出力
 sheet.getRange(`A${fromRow}:C${toRow}`).setValues(output);
}
【ポイント1】
const lastRow = sheet.getLastRow();
シートに存在する一番最後の行数を取得。

【ポイント2】
const fromRow = lastRow + 1; 
const toRow = lastRow + output.length;
出力する行数を指定（最終行の次なので+1をしています）

【ポイント3】
sheet.getRange(`A${fromRow}:C${toRow}`).setValues(output);
これはただの文字列結合。'A5:C6' みたいな文字列を作っているだけです。

ちなみに、1行分のデータを末尾に付けたいという時は、「appendRow()」を使います。必要なときに検索をして使い方を調べてみましょう！

というか今日の宿題で出題したいと思います(｀・ω・´)ゞ

getRange()をもう少し深く知っておこう

↑これは、公式のリファレンス。
getRange()が4種類ありますね。
このようにgetRangeで範囲を取得する方法は、4種類存在しています。

画像4

【1】getRange(row, column)はgetRange(行,列)のこと

function getRangeSample1() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 const range = sheet.getRange(2, 3); 
 const value = range.getValue(); // 値が1つのときはgetValue
 
 console.log(value);
}

getRange(2, 3);
つまり、2行目3列目（C3セル）のこと。

【2】getRange(row, column, numRows) 

function getRangeSample2() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 const range = sheet.getRange(1, 2, 3);
 const values = range.getValues(); //複数の値なので getValues
 
 console.log(values);
}

getRange(1, 2, 3);
つまり、1行目2列目ぼ3行分（B1:B3）のこと。

【3】getRange(row, column, numRows, numColumns)

function getRangeSample3() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 const range = sheet.getRange(2, 2, 3, 2);
 const values = range.getValues();
 
 console.log(values);
}

getRange(2, 2, 3, 2);
つまり、2行目2列目から3行2列分。
開始行、開始列、何行選択するか、何列選択するか。

【4】getRange(a1Notation)

function getRangeSample4() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 const range = sheet.getRange("B2:C3");
 const values = range.getValues();
 
 console.log(values);
}

getRange("B2:C3");
もうそのままですね。

では宿題です！

function setSomeValues() {
 const ss= SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName('シート1');
 
 const output = [
   ['あああ','いいい','ううう'],
   ['えええ','おおお','かかか']
 ];
 
 sheet.getRange('A1:C2').setValues(output);
}

上記シートに出力した後、「appendRow()」を使って1行追加をしてみてください。追加をする文字は ['ききき','くくく','けけけ']とします。

宿題は以上です！

そして次はスプレットシート編のテストとなります！
ではまた次回、よろしくお願いいたします(｀・ω・´)ゞ


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