【GAS】初心者向けスプレッドシート操作まとめ

スプレッドシートとGoogle Apps Scriptの連携

出来た事、VBAからの移植でハマったところを中心に記述します。

スプレッドシートの値を取得する１

・Excel VBAだと

strData = Worksheets("WORK1").Range("B3").Value

・GASだと

var strData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('WORK1').getRange('B3').getValue();

スプレッドシートの値を取得する２

・Excel VBAだと

Worksheets("WORK1").Range("B3").Value = Worksheets("WORK2").Range("D2").Value

※WORK2シートのD2の値をWORK1のB3セルにセットする。

・GASだと

var data1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('WORK2').getRange('D2').getValue();

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('WORK1').getRange('B3').setValue(data1 );

※setValueでセルに値をセットする。

MENUシートのD5～D34のセル範囲内から文字列 "おでん"の位置を検索する

  var arrayBefore;
  var arrayAfter;

  var sht = SpreadsheetApp.getActiveSpreadsheet();
  arrayBefore = sht.getSheetByName('MENU').getRange(5, 4, 30).getValues();
  arrayAfter = Array.prototype.concat.apply([], arrayBefore);
  out_idx = arrayAfter.indexOf('おでん');

getValues()は２次元配列なので、１次元配列に変換してからindexOfで検索。
範囲内に無い場合は-1を返す。先頭行がヒットした場合、結果は0となる。

文法について

if文のANDは　&& で記述する。
ORは || で記述する。
○○じゃない　は　 != で記述する。（<> はダメ）

B3セルに入力されている、20180101 を日付に変換する場合

work_date1 = todayDataSheet.getRange(3,2).getValue();
work_date2 = work_date1.toString().substr(0,4) + '/' + work_date1.toString().substr(4,2) + '/' + work_date1.toString().substr(6,2);

※toString()が大事。

特定のシート以外のシートを削除したい

function deleteSheetTest() {
  /* シートを削除。原本とMENU以外のシートを全て削除 */
  var workss = SpreadsheetApp.getActiveSpreadsheet();
  var intCount;
  
  if (workss.getSheets().length >= 1) {
    intCount = workss.getSheets().length;
    
    for(var i = intCount -1 ;i >= 0; i--)
    {
        if(!workss.getSheets()[i].getName().match(/MENU|原本/))
        {
          var wwsheet = workss.getSheetByName(workss.getSheets()[i].getName());
          workss.deleteSheet(wwsheet);        
        }
    }
  }
  Browser.msgBox('シート削除完了！');
}

原本とMENUシート以外のシートを削除する。
※シート数だけループする際、最後のシートから判断してdeleteSheetしないとダメ。
for(var i = 0 ;i < intCount; i++) だとうまくいかない。

原本シートをコピーして、所定のシート名に変更したい

function createSheetTest() {
  /* シートを作成。同名のシートがある場合、飛ばす */
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var templateSheet = ss.getSheetByName('原本'); 
  var strCreateSheetName;
  // シートをコピー
  for(var i = 1;i < 6; i++)
  {
    strCreateSheetName = ss.getSheetByName('MENU').getRange(i + 2,3).getValue();
    if (strCreateSheetName != ""){
      var sht = ss.getSheetByName(strCreateSheetName);
      if(!sht){
      ss.insertSheet(strCreateSheetName, ss.getSheets().length, {template: templateSheet});
      ss.getSheetByName(strCreateSheetName).getRange(2, 3).setValue(strCreateSheetName);
      }    
    }
  }
  ss.getSheetByName('MENU').getRange('A1').activate();
  Browser.msgBox('シート作成完了！');
}

MENUシートのC3:C7にある文字列でシートを作成。その際、原本シートをコピーする。
すでに同名のシートがある場合は飛ばす。
さらに、作成したシートのC2セルにシート名をセットする。

ファイル選択ダイアログを使いたい（その１）

　～ファイル選択編～

Excel VBAでよく使う、「ファイル選択ダイアログ」を表示させて、別のエクセルファイル
を選択して、現在のエクセルファイルに値をコピーする。　というものを実現したい。

参考サイトを２つご紹介

参考サイト１（日本語）→　Pickerでファイルやフォルダを選択する画面を装備する

参考サイト２（英語）→　Using Google Picker with Google Apps Script
↑こっちは英語。しかし関数とかは共通してるので、移植はがんばればできる。

・APIの使用方法は参考サイト１に記述。APIキーの取得だけでOKっぽかった。
・注意点はPicker.htmlの作成方法。
　GASの画面（.gsを編集している画面）のファイル→新規作成→HTMLファイル
　でPicker.htmlを作成しないとダメ。

（VBAで例えると、コード表示させてから挿入→ユーザーフォームと似た感じ）

上記の参考サイトを基にコードを作成すると、ファイル選択ダイアログがPOPUPされ、エクセルファイルの選択ができ、そのファイルID,URLなどが取得できる。

※次は取得できたファイルを元ファイルにコピーするところから開始予定です。

ファイル選択ダイアログを使いたい（その２）

　～ファイルを開いてコピー編～

参考サイト
別のスプレッドシートにデータをコピーするには

確認したとこ
pickerCallback（Picker.html）の google.script.run.telepon(id);
これで、GAS側に取得したスプレッドシートのIDが渡る。
受け側のteleponに　上記参考サイトの関数（copyValues）の内容を記述すればOK。

ここで注意点：
COPYFROM（コピー元）はファイル選択ダイアログで選択したファイル
COPYTO（コピー先）はGASを実行しているファイル
なので、FROMとTOを以下のように修正。

var ss_copyTo = SpreadsheetApp.getActiveSpreadsheet();
var ss_copyFrom = SpreadsheetApp.openById(sheetid);

まとめ

　今のところ以下の処理がスプレッドシート＋GASで実現できた。
　・セル範囲の値のコピー
　・セル範囲から値を検索し、その位置を取得
　・シートの追加とシートの削除（所定のシート以外を全て削除）
　・ファイル（スプレッドシート）を選択し、そのスプレッドシートの特定のシートの値を
　　現在開いているシートにセットする。

　なので、スプレッドシートＡのデータをスプレッドシートＢのデータを検索してセットするという事もできるようになった。
　
　これでだいぶエクセルマクロで実現していたことがスプレッドシート＋GASでもできるようになった。