A Collection of Methods for Manipulating Google Sheets with GAS

Introduction

Hello everyone,
This is Ookuma.

In this article, I have compiled various methods for manipulating Google Sheets using GAS (Google Apps Script) under the title "Miscellaneous Methods for Operating Sheets with GAS."
I hope this proves useful to you.

Target Audience

• Those interested in GAS
• Those interested in methods for manipulating Google Sheets

Note

• The content may contain errors.
• I would greatly appreciate it if you could point them out in the comments.

What is GAS?

GAS stands for Google Apps Script, a scripting platform developed by Google.
It is utilized as a tool for developing applications to automate and integrate various services provided by Google.
The scripting language used in GAS is based on JavaScript.
Since the basic syntax is the same as JavaScript, those who have used JavaScript before should find it relatively easy to master.

https://satori.marketing/marketing-blog/gas-introduction/

At our company, we also use GAS to automate and streamline various business tasks.

Methods for Operating Sheets

Getting a Spreadsheet

Get the currently open spreadsheet

function getSpreadsheet() {
  return SpreadsheetApp.getActiveSpreadsheet();
}

Get a spreadsheet by its ID

function getSpreadsheetById(id) {
  return SpreadsheetApp.openById(id);
}

Get a spreadsheet by its URL

function getSpreadsheetByUrl(url) {
  return SpreadsheetApp.openByUrl(url);
}

Getting a Sheet

Get the currently active sheet

function getActiveSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
}

Get a sheet by its name

function getSheetByName(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

Get a sheet by name (create if it does not exist)

function getSheetByNameOrCreate(sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  }
  return sheet;
}

Get all sheets with names partially matching the input

function getSheetsByNameContains(sheetName) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  return sheets.filter(function(sheet) {
    return sheet.getName().indexOf(sheetName) !== -1;
  });
}

Get a sheet by its index

function getSheetByIndex(index) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[index];
}

Adding a Sheet

Add a sheet

function addSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().insertSheet();
}

Add a sheet by specifying a name

function addSheet(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}

Add a sheet by specifying an index

function addSheetWithIndex(index) {
  return SpreadsheetApp.getActiveSpreadsheet().insertSheet(index);
}

Deleting a Sheet

Delete the currently active sheet

function deleteSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().deleteActiveSheet();
}

Delete a sheet by specifying its name

function deleteSheet(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().deleteSheet(getSheetByName(sheetName));
}

function getSheetByName(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

Delete a sheet by specifying an index

function deleteSheetByIndex(index) {
  return SpreadsheetApp.getActiveSpreadsheet().deleteSheet(getSheetByIndex(index));
}

function getSheetByIndex(index) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[index];
}

Copying a Sheet

Copy the currently active sheet

function copySheet() {
  return SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();
}

Copy a sheet by specifying its name

function copySheetByName(sheetName, newSheetName) {
  var sheet = getSheetByName(sheetName);
  var newSheet = sheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  newSheet.setName(newSheetName);
  return newSheet;
}

function getSheetByName(sheetName) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
}

Copy a sheet by specifying an index

function copySheetByIndex(index, newSheetName) {
  var sheet = getSheetByIndex(index);
  var newSheet = sheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  newSheet.setName(newSheetName);
  return newSheet;
}

function getSheetByIndex(index) {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[index];
}

Sorting Sheets

Sort sheets in ascending order by name

function sortSheetsByNameAsc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  sheets.sort(function(a, b) {
    return a.getName().localeCompare(b.getName());
  });
  for (var i = 0; i < sheets.length; i++) {
    spreadsheet.setActiveSheet(sheets[i]);
    spreadsheet.moveActiveSheet(i + 1);
  }
}

Sort sheets in descending order by name

function sortSheetsByNameDesc() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  sheets.sort(function(a, b) {
    return b.getName().localeCompare(a.getName());
  });
  for (var i = 0; i < sheets.length; i++) {
    spreadsheet.setActiveSheet(sheets[i]);
    spreadsheet.moveActiveSheet(i + 1);
  }
}

Entering Values into Cells

Enter a value into a cell

function setCellValueInActiveSheet(cellAddress, value) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).setValue(value);
}

Setting Hyperlinks in Cells

Set a hyperlink in a cell

function setHyperlinkInActiveSheet(cellAddress, url, label) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).setFormula('=HYPERLINK("' + url + '","' + label + '")');
}

Getting Values from Cells

Get a value from a cell

function getCellValueInActiveSheet(cellAddress) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  return sheet.getRange(cellAddress).getValue();
}

Clearing Cell Values

Clear a cell value

function clearCellValueInActiveSheet(cellAddress) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).clearContent();
}

Copying Cell Values

Copy a cell value

function copyCellValueInActiveSheet(fromCellAddress, toCellAddress) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var value = sheet.getRange(fromCellAddress).getValue();
  sheet.getRange(toCellAddress).setValue(value);
}

Merging Cells

Merge cells

function mergeCellsInActiveSheet(startRow, startColumn, numRows, numColumns) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(startRow, startColumn, numRows, numColumns).merge();
}

Setting Cell Background Color

Set cell background color

function setBackgroundColorInActiveSheet(cellAddress, color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).setBackground(color);
}

Setting Cell Font Color

Set cell font color

function setFontColorInActiveSheet(cellAddress, color) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).setFontColor(color);
}

Setting Cell Font Size

Set cell font size

function setFontSizeInActiveSheet(cellAddress, fontSize) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(cellAddress).setFontSize(fontSize);
}

Conclusion

In this article, I have compiled various methods for operating Google Sheets using GAS.
For example, when writing data obtained via API to Google Sheets, using the methods above allows for efficient processing.
Please feel free to use them.
Thank you for reading until the end.

References

https://caymezon.com/gas-ss-methods-list/

https://qiita.com/mitama/items/e5fbf8306384c26cf42f

https://qiita.com/mori_goq/items/449ece2ca2178cff1fa7