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

参考サイトを２つご紹介

参考サイト１（日本語）→　<a href="https://officeforest.org/wp/2018/05/20/picker%E3%81%A7%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%84%E3%83%95%E3%82%A9%E3%83%AB%E3%83%80%E3%82%92%E9%81%B8%E6%8A%9E%E3%81%99%E3%82%8B%E7%94%BB%E9%9D%A2%E3%82%92%E8%A3%85%E5%82%99%E3%81%99/">Pickerでファイルやフォルダを選択する画面を装備する</a>

参考サイト２（英語）→　<a href="https://ctrlq.org/code/20039-google-picker-with-apps-script">Using Google Picker with Google Apps Script</a>
↑こっちは英語。しかし関数とかは共通してるので、移植はがんばればできる。


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

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

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


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

#ファイル選択ダイアログを使いたい（その２）
　～ファイルを開いてコピー編～

参考サイト
<a href="http://www.bmoo.net/archives/2012/07/314383.html">別のスプレッドシートにデータをコピーするには</a>


確認したとこ
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でもできるようになった。