Snippets · 12 functions
Google Sheets snippets
Twelve short functions for the Sheets tasks that come up again and again: reading rows, writing values, and shaping 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
Look up a Sheet tab by its name and throw a clear error instead of guessing 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
Read all the data on a tab in one call so you can loop over it in memory instead of calling the Sheet repeatedly.
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
Pull a single column of values, such as email addresses, into a plain list instead of a 2D array.
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
Add a single new row to the bottom of a tab without needing to know the last row number yourself.
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
Write 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
Search a column for a matching value and return 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
Remove the values from a range while leaving colors, borders, and number formats in place.
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
Write a formula into a cell so the Sheet calculates a value instead of the script computing it.
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
Format row 1 as bold and freeze it in place so headers stay visible while a reader scrolls.
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
Sort 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
Check for a tab by name first and only insert a new one 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
Use row 1 as property names so each data row becomes an object you can read by name instead of by column number.
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
Use one row per person, then let the script send only the rows that have not already been marked as sent.
- Task guide Copy unique rows to a clean tab
The script reads your list, keeps the first row for each email address, and rewrites a tab named Clean. The source tab is never changed.
- Task guide Move finished rows to an Archive tab
Rows with the status Done are copied to the bottom of an Archive tab and then removed from Tasks. This one deletes rows, so test it on a copy of the file first.
- Task guide Add a timestamp when a row changes
Change a status cell and the script stamps the same row with the current date and time. There is nothing to install beyond saving the script.
- Basics Rows, columns, and 2D arrays in Sheets scripts
A range is a rectangle of cells. getValues() hands you 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 name the failure directly, from TypeError: Cannot read properties of null (reading 'getRange') to ReferenceError and quota limits. Find the exact message below for the cause and fix.