Skip to content

Instantly share code, notes, and snippets.

View Code.gs
function onOpen( ){
// This line calls the SpreadsheetApp and gets its UI
// Or DocumentApp or FormApp.
var ui = SpreadsheetApp.getUi();
//These lines create the menu items and
// tie them to functions we will write in Apps Script
ui.createMenu('Học Excel Online')
.addItem('1. formulasToValuesActiveSheet', 'formulasToValuesActiveSheet')
View resetFilter.gs
//10 reset all filters for a data range on current Sheet
function resetFilter() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.getFilter().remove();
range.createFilter();
}
View unhideAllSheets.gs
//9. Unhide all sheets
function unhideAllSheets() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.showSheet();
});
};
View hideAllSheetsExceptActive.gs
//8. hide all sheets except the active one
function hideAllSheetsExceptActive() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
if (sheet.getName() != SpreadsheetApp.getActiveSheet().getName())
sheet.hideSheet();
});
};
View resetTabColor.gs
//7. remove all Sheets tabs color
function resetTabColor() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.setTabColor(null);
});
};
View setTabColor.gs
//6. set all Sheets tabs to red
function setTabColor() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets.forEach(function(sheet) {
sheet.setTabColor("ff0000");
});
};
View unhideRowsColumnsGlobal.gs
//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);
});
};
View unhideRowsColumnsActiveSheet.gs
//4. unhide all rows and columns in current Sheet data range
function unhideRowsColumnsActiveSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
sheet.unhideRow(range);
sheet.unhideColumn(range);
}
View sortSheets.gs
//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();
View formulasToValuesGlobal.gs
//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());
});
};