スプレッドシートでよく使うGAS関数一覧！具体例でわかる使い方

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ');

 let sheet = SpreadsheetApp.getActiveSheet();

  let ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.insertSheet(); // スプレッドシートの末尾に新しいシートを挿入

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('古いデータ');
  if (sheet) {
    SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet); // シートを削除
  } else {
    Logger.log("シートが見つかりませんでした。");
  }

  let sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let newSpreadsheet = SpreadsheetApp.create('新しいスプレッドシート');
  sourceSheet.copyTo(newSpreadsheet);

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  let range = sheet.getRange('A1:B5');
  Logger.log(range.getValues()); // 取得したセル範囲の値をログに表示

[  [ 'Apple', 100 ],
  [ 'Banana', 150 ],
  [ 'Orange', 120 ],
  [ 'Grape', 200 ],
  [ 'Peach', 180 ]
]

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  let range = sheet.getRange('A1:B5');
  let cellValues = range.getValues();
  Logger.log(cellValues); // A1セルからB5セルまでの値をログに表示

[  [ 'Apple', 100 ],
  [ 'Banana', 150 ],
  [ 'Orange', 120 ],
  [ 'Grape', 200 ],
  [ 'Peach', 180 ]
]

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  sheet.getRange('C3').setValue('Hello, World!');

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  let valuesToSet = [
    ['Cherry', 130, 'Red', 'USA'],
    ['Lemon', 90, 'Yellow', 'Mexico']
  ];
  let range = sheet.getRange('A6:D7');
  range.setValues(valuesToSet);

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  sheet.getRange('E1').clearContent();

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SampleSheet');
  let range = sheet.getRange('A1:B2');
  range.setBackground('blue');

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.appendRow(['Mango', 25, 180]);

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
| Apple     |    10     |   100     |
| Orange    |    15     |   120     |
| Banana    |    20     |   150     |
| Grapes    |    12     |   200     |
| Mango     |    25     |   180     |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.insertRowBefore(3); // 3行目の前に新しい行が挿入される

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
| Apple     |    10     |   100     |
|           |           |           |  ← 新しい行
| Orange    |    15     |   120     |
| Banana    |    20     |   150     |
| Grapes    |    12     |   200     |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.deleteRow(2); // 2行目が削除される

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
| Orange    |    15     |   120     |
| Banana    |    20     |   150     |
| Grapes    |    12     |   200     |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.appendColumn([1000, 1800, 3000, 2400]);

|   A       |   B        |   C       |   D   |
|-----------|-----------|-----------|-------|
|  Product  |  Quantity |  Price    | Total |
| Apple     |    10     |   100     | 1000  |
| Orange    |    15     |   120     | 1800  |
| Banana    |    20     |   150     | 3000  |
| Grapes    |    12     |   200     | 2400  |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.deleteColumn(2); // 2列目が削除される

|   A       |   C       |   D   |
|-----------|-----------|-------|
|  Product  |  Price    | Total |
| Apple     |   100     | 1200  |
| Orange    |   120     | 1800  |
| Banana    |   150     | 3000 |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  let dataRange = sheet.getDataRange();
  Logger.log(dataRange.getValues()); // データの範囲をログに表示

[  [ 'Product', 'Quantity', 'Price' ],
  [ 'Apple', 10, 100 ],
  [ 'Orange', 15, 120 ],
  [ 'Banana', 20, 150 ],
  [ 'Grapes', 12, 200 ],
  [ 'Apple', 8, 95 ],
  [ 'Peach', 5, 180 ]
]

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  let textFinder = sheet.createTextFinder('Apple');
  let matches = textFinder.findAll();
  Logger.log(matches.map(function(match) { return match.getValue(); })); // 検索結果をログに表示

[ 'Apple', 10, 100 ]
[ 'Apple', 8, 95 ]

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  let data = sheet.getDataRange().getValues();
  let keyColumnIndex = 0; // Product列が0番目の列(A列)にあると仮定
  let searchValue = 'Peach';
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][keyColumnIndex] === searchValue) {
      Logger.log(data[i]);
    }
  }

[ 'Peach', 5, 180 ]

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  sheet.getRange('A2:C8').sort(1); // 1列目（Product列）を昇順でソート

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
|  Apple    |    10     |   100     |
|  Apple    |    8      |   95      |
|  Banana   |    20     |   150     |
|  Grapes   |    12     |   200     |
|  Orange   |    15     |   120     |
|  Orange   |    18     |   110     |
|  Peach    |    5      |   180     |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  let textFinder = sheet.createTextFinder('Apple');
  let matches = textFinder.findAll();
  Logger.log(matches.map(function(match) { return match.getValue(); })); // 検索結果をログに表示

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
|  Banana   |    20     |   150     |
|  Orange   |    18     |   110     |
|  Orange   |    15     |   120     |
|  Grapes   |    12     |   200     |
|  Apple    |    10     |   100     |
|  Apple    |    8      |   95      |
|  Peach    |    5      |   180     |

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SalesData');
  let range = sheet.getRange('A1:C8');
  let columnIndex = 2; // Price列が2番目の列(C列)にあると仮定
  let filterCriteria = SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(100).build();
  sheet.getFilter().setColumnFilterCriteria(columnIndex, filterCriteria);

|   A       |   B        |   C       |
|-----------|-----------|-----------|
|  Product  |  Quantity |  Price    |
| Banana    |    20     |   150     |
| Grapes    |    12     |   200     |
| Orange    |    18     |   110     |
| Peach     |    5      |   180     |