Snippets · 12 functions
Google Sheets snippets
Twelve short functions for common Sheets tasks: reading rows, writing values, and formatting a tab.
How to use these
Set up before you run
Start with a test spreadsheet and open Extensions > Apps Script from that spreadsheet. These snippets use the bound spreadsheet, not a standalone project. Create the tab named in the snippet, such as Customers, and add the headers and sample rows it needs before running it.
Copy a snippet into a script file, read its setup and notes, change the placeholder values, and save. For functions meant to run manually, choose the function name in the toolbar and press Run. Results appear in the Execution log unless the description says otherwise.
1
Get a tab by name and stop if missing
Looks up a Sheet tab by its name and throws an error that names the tab when it does not exist.
function getCustomersTab() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
Logger.log(sheet.getName());
}2
Read every row as a 2D array
Reads all the data on a tab in one call, so you can loop over it in memory instead of calling the Sheet for each row.
function readAllCustomerRows() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const values = sheet.getDataRange().getValues();
Logger.log(values);
}
Good to know. The first row in the array is usually your headers, so skip row 0 when you loop over data.
3
Read one column as a flat list
Reads a single column of values, such as email addresses, into a flat list.
function readEmailColumn() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const column = 2;
const emails = sheet.getRange(2, column, lastRow - 1, 1).getValues().map((row) => row[0]);
Logger.log(emails);
}4
Append one row
Adds one row below the last row of a tab. You do not need to know the last row number.
function appendCustomerRow() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
sheet.appendRow(['Jamie Rivera', 'jamie@example.com', 'New']);
Logger.log('Row added at ' + sheet.getLastRow());
}5
Write a block of values with setValues
Writes a whole rectangle of values in one call, which is faster than writing cell by cell.
function writeCustomerBlock() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const values = [
['Jamie Rivera', 'jamie@example.com'],
['Alex Chen', 'alex@example.com']
];
sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
Logger.log('Wrote ' + values.length + ' rows.');
}Good to know. The range you write to must have exactly as many rows and columns as the array, or the call throws an error.
6
Find the row number for a value
Searches a column for a matching value and returns its Sheet row number, so you can update that row.
function findRowForEmail() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const targetEmail = 'jamie@example.com';
const emails = sheet.getRange(1, 2, sheet.getLastRow(), 1).getValues();
const index = emails.findIndex((row) => row[0] === targetEmail);
if (index === -1) throw new Error('No row has the email ' + targetEmail + '.');
const rowNumber = index + 1;
Logger.log('Found on row ' + rowNumber);
return rowNumber;
}7
Clear a range but keep formatting
Removes the values from a range and keeps its colors, borders, and number formats.
function clearCustomerValuesOnly() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent();
Logger.log('Cleared values below the header row.');
}8
Set a formula in a cell
Writes a formula into a cell, so the Sheet calculates the value.
function setTotalFormula() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
sheet.getRange(lastRow + 1, 3).setFormula('=SUM(C2:C' + lastRow + ')');
Logger.log('Formula set on row ' + (lastRow + 1));
}9
Make the header row bold and freeze it
Makes row 1 bold and freezes it, so the headers stay visible when you scroll.
function formatCustomerHeaderRow() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
headerRange.setFontWeight('bold');
sheet.setFrozenRows(1);
Logger.log('Header row formatted and frozen.');
}10
Sort a data range by a column
Sorts the rows below the header in ascending order by one column, such as putting names in alphabetical order.
function sortCustomersByName() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const lastRow = sheet.getLastRow();
if (lastRow < 2) return;
const dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
const nameColumn = 1;
dataRange.sort(nameColumn);
Logger.log('Sorted by column ' + nameColumn + '.');
}Good to know. Sorting a range only reorders the rows inside it, so include every column you want to stay lined up with each row.
11
Create a tab only if it does not exist
Checks for a tab by name and inserts a new one only when it is missing, so a repeat run reuses the existing tab.
function getOrCreateArchiveTab() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spreadsheet.getSheetByName('Archive');
if (!sheet) {
sheet = spreadsheet.insertSheet('Archive');
Logger.log('Created a new Archive tab.');
} else {
Logger.log('Archive tab already exists.');
}
}12
Turn rows into objects keyed by header
Uses the headers in row 1 as property names, so you can read each value in a data row by its header name.
function readCustomersAsObjects() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');
const values = sheet.getDataRange().getValues();
const headers = values[0];
const rows = values.slice(1).map((row) => {
/** @type {Record<string, unknown>} */
const record = {};
headers.forEach((header, index) => {
record[String(header)] = row[index];
});
return record;
});
Logger.log(rows);
}Keep going
See these in a complete task
- Task guide Send an email for each row in a Google Sheet
Send one email to each person listed on a Customers tab. The script marks each row Sent as it goes, so a later run sends only to rows that are not marked yet.
- Task guide Copy unique rows to a clean tab
Remove duplicate people from a contact list. The script keeps the first row for each email address and writes those rows to a tab named Clean, leaving the original list as it is.
- Task guide Move finished rows to an Archive tab
Move every row marked Done from a Tasks tab to the bottom of an Archive tab. The script deletes rows from Tasks, so test it on a copy of the file first.
- Task guide Add a timestamp when a row changes
When you change a status cell, the script writes the current date and time into the same row. You save the script and nothing else needs to be installed.
- Basics Rows, columns, and 2D arrays in Sheets scripts
A range is a rectangle of cells. getValues() returns that rectangle as an array of rows, and each row is an array of cell values.
- Basics Common Apps Script errors and what they mean
Apps Script error messages say what failed, for example TypeError: Cannot read properties of null (reading 'getRange') or ReferenceError. Find the exact message below for its cause and fix.