Writing Code in Google Sheets for Efficient and Quick Workflow

Google Sheets is growing in strength and has become an indispensable tool for most organizations and agencies. However, performing repetitive tasks in Google Sheets can be tedious, and you might find yourself wishing for a tool to automate these tasks. Below are 10 code snippets in Google Sheets to create macros that eliminate the need for repetitive tasks, making your work much faster and more efficient.

Writing Code for Macros in Google Sheets

Code for creating a macro to convert formulas to values on the current sheet in Google Sheets:

javascriptCopy code//1. Convert all formulas to values in the active sheet
function formulasToValuesActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
};

Code for creating a macro to convert formulas to values on all sheets in the Google Sheets workbook:

javascriptCopy code//2. Convert all formulas to values in every sheet of the Google Sheet
function formulasToValuesGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    range.copyValuesToRange(sheet, 1, range.getLastColumn(), 1, range.getLastRow());
  });
};

Code for creating a macro to sort sheets alphabetically:

javascriptCopy code//3. Sort sheets alphabetically
function sortSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheetNames = [];
  sheets.forEach(function(sheet, i) {
    sheetNames.push(sheet.getName());
  });
  sheetNames.sort().forEach(function(sheet, i) {
    spreadsheet.getSheetByName(sheet).activate();
    spreadsheet.moveActiveSheet(i + 1);
  });
};

Writing Code for Macros in Google Sheets

Code for creating a macro to unhide rows and columns in the current sheet’s data range:

javascriptCopy code//4. Unhide all rows and columns in the current sheet's data range
function unhideRowsColumnsActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  sheet.unhideRow(range);
  sheet.unhideColumn(range);
}

Code for creating a macro to unhide rows and columns in the data ranges of all sheets in the Google Sheets workbook:

javascriptCopy code//5. Unhide all rows and columns in data ranges of entire Google Sheet
function unhideRowsColumnsGlobal() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    sheet.unhideRow(range);
    sheet.unhideColumn(range);
  });
}

Code for creating a macro to set the tab color of all sheets in Google Sheets to red:

javascriptCopy code//6. Set all Sheets tabs to red
function setTabColor() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.setTabColor("ff0000");
  });
}

Code for creating a macro to unhide all hidden sheets in Google Sheets:

javascriptCopy code//9. Unhide all sheets
function unhideAllSheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(function(sheet) {
    sheet.showSheet();
  });
}

Writing code in Google Sheets to create macros can enhance your productivity, efficiency, and time savings, avoiding tedious repetition. Wishing you success in applying these techniques to your work.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top