Google Spread Sheetのマクロ(Google Apple Script[GAS])の基本 | エクセルマクロ(VBA)実践蔵(じっせんぐら)

トップ項目一覧

Google Spread Sheetで基本になるコードを載せて行こうと思います。トリガの設定や自動メール送信については、GASで株価の取得やトリガの設定、自動メール送信などについてに記述します。

• このページの目次
• GASのセルのデータ取得とセルのデータ設定方法＜行と列でループ＞
• GASで参照する行数が可変の場合
• GASで関数を指定して実行する方法
• GASから開いていないシートを操作する方法
• GASでセルのデータと関係なく、配列のデータをセルに書き込む
• GASでFor Eachは使えるか？

GASのセルのデータ取得とセルのデータ設定方法＜行と列でループ＞

エクセルマクロに慣れている人向けに書いてみました。試しに変数名を日本語にしてみましたが、問題なく実行できました。

エクセルマクロとGoogle Spread Sheet(GAS)の大きな違いとして、エクセルマクロがPC上で動作するのに対して、GASはサーバ上で動作します。管理人は、Googleの無料枠内での利用の為、連続処理時間に制限があります。同じ環境の方は、セルの内容をまとめて配列入れて、配列を更新し、セルを更新するのをおすすめします。1セルずつアクセスして書き込みを行うとどうしても処理速度は遅くなります。

ただし、デバッグで状況確認するには、配列の方がしずらいです。セルに書いてしまった方が、状況がわかりやすいです。

あと、エクセルマクロは、英字の大文字小文字の区別をしませんが、GASの方は、別扱いになるので、気を付けてください。「SpreadsheetApp」や「getRange」などの決められているメソッド(関数)は、大文字を小文字にしてしまうとエラーになります。

変数は、「let」が便利です。数値も文字列も配列もletで可能です。配列は、0スタートになります。そのため、1行目1列目のデータが配列[0][0]に格納されます。

変数「const」は、初期値から変更できない変数です。「let」でも代用は可能です。

getValues()で、セル内のデータが配列に格納されます。

 
function test() 
{ 
  let Book = SpreadsheetApp.getActiveSpreadsheet(); 
  let Sheet = Book.getSheetByName("シート1"); 
  let Table = Sheet.getRange("A1:C7"); 
  let 表配列 = Table.getValues(); 
  let 行数; 
  let 列数; 
  let 文字列 = "書き込み"; 
  const offset=1; 
 
  for( 行数 = 0; 行数 < Table.getLastRow() ; 行数++  ) 
  {   
    for( 列数 = 0; 列数 < Table.getLastColumn() ; 列数++  ) 
    {    
      if(行数 === 0) 
      { 
        表配列[行数][列数]=列数+1; 
      } 
      if( 列数 === 0 ) 
      { 
        表配列[行数][列数]=行数+1; 
      } 
      // 4行目 2列目 への書き込み 
      if( 行数+offset === 4 && 列数+offset === 2 ) 
      { 
        表配列[行数][列数]=文字列; 
      } 
    } 
  } 
  Table.setValues(表配列); 
} 

GASで参照する行数が可変の場合

シート内データをすべて配列に格納して、データを更新する。

 
 
function test2() 
{ 
  let Book = SpreadsheetApp.getActiveSpreadsheet(); 
  let Sheet = Book.getSheetByName("シート1"); 
  let AllRange = Sheet.getRange(1,1,Sheet.getLastRow(),Sheet.getLastColumn()); 
  let 全データ = AllRange.getValues(); 
   
  // 全データ[][]配列の編集 
   
  AllRange.setValues(全データ); 
} 

GASで関数を指定して実行する方法

自作した関数の実行方法は、下記の▼をクリックし、関数を選択後に実行する。

GASから開いていないシートを操作する方法

ActiveSpreadsheet以外のシートへのアクセス方法は、IDによるものになります。

IDはURLの中に含まれています。「https://docs.google.com/spreadsheets/d/」の後ろから「/edit?gid=0#gid=0」の前までが、IDになります。「～/d/ここがID/edit～」

関数化しておくと、便利です。戻り値ありの変数宣言は、関数内にreturn ○○; と記述すれば、呼び出し側で受け取れます。配列を戻したい場合は、配列名を指定すれば、配列も関数から戻せます。

 
function getMySheet(){ 
  let Book = SpreadsheetApp.openById("XXX"); 
  let Sheet = Book.getSheetByName("シート1"); 
   
  return Sheet; 
} 
 
function 呼び出し側(){ 
  let sht = getMySheet(); 
 
} 

GASでセルのデータと関係なく、配列のデータをセルに書き込む

作成した配列データをセルに書き込む

 
function データ設定(){ 
  let Book = SpreadsheetApp.getActiveSpreadsheet(); 
  let Sht =  Book.getSheetByName("シート2"); 
 
  let WRow = 5; //書き込み行数 
  let WCol = 5; //書き込み列数 
  let val = [[1,2,3],[4,5,6],[7,8,9]]; //配列のデータを作成 
   
  // 書き込み 開始行、開始列、書き込み行数(配列サイズ(行))、書き込み列数(配列サイズ(列)) 
  Sht.getRange(WRow,WCol,val.length,val[0].length).setValues(val); 
 
  return; 
} 

GASでFor Eachは使えるか？

健忘録として、For Eachの書き方を下記にのせます。配列をひとつづつ取り出すコードです。ただ、配列の何番目を更新したのか情報が必要な場合、アドレスを意識して求めないといけないので、結局、行数や列数で回した方がよさそうでした。

 
function test() 
{ 
  let Book = SpreadsheetApp.getActiveSpreadsheet(); 
  let Sheet = Book.getSheetByName("シート2"); 
 
  let Table = Sheet.getRange("A1:C7"); 
  let 表配列 = Table.getValues(); 
  let 行数; 
 
  for( 行数 = 0; 行数 < Table.getLastRow() ; 行数++ ) 
  {   
    for( let colcnt of 表配列[行数] ) 
    {    
        Logger.log("テスト 行数"+行数+" 値"+colcnt); 
    } 
  } 
}