Why These Snippets Matter

SpreadsheetApp is the most commonly used service in Google Apps Script. Whether you're reading form responses, updating a dashboard, or processing a list of records, these foundational snippets will save you hours. Copy, paste, and adapt them to your own projects.

1. Get the Active Sheet and Read All Data

function readAllData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  data.forEach((row, index) => {
    Logger.log(`Row ${index + 1}: ${row}`);
  });
}

getDataRange() returns all cells that contain data. getValues() returns a 2D array — rows are the outer array, columns are inner.

2. Write Data to a Specific Cell or Range

function writeData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Write to a single cell
  sheet.getRange("A1").setValue("Hello, World!");
  
  // Write a 2D array to a range
  const values = [
    ["Name", "Email", "Score"],
    ["Alice", "alice@example.com", 95],
    ["Bob",   "bob@example.com",   88]
  ];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Pro tip: Always prefer setValues() over multiple setValue() calls. Batch writes are dramatically faster.

3. Find the Last Row with Data

function getLastRow() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  
  Logger.log(`Data extends to row ${lastRow}, column ${lastCol}`);
  return lastRow;
}

4. Append a New Row

function appendRow() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const newRow = ["2024-06-01", "New Entry", "Pending", 0];
  sheet.appendRow(newRow);
}

appendRow() adds data immediately after the last row that contains content — no need to calculate row numbers manually.

5. Search for a Value in a Column

function findValue(searchTerm) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const columnData = sheet.getRange("A:A").getValues();
  
  for (let i = 0; i < columnData.length; i++) {
    if (columnData[i][0] === searchTerm) {
      Logger.log(`Found "${searchTerm}" at row ${i + 1}`);
      return i + 1; // 1-indexed row number
    }
  }
  return -1; // Not found
}

6. Clear a Range Without Deleting the Row

function clearRange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  sheet.getRange("B2:D10").clearContent();   // Clear values only
  // sheet.getRange("B2:D10").clear();        // Clear values + formatting
}

7. Format Cells Programmatically

function formatHeader() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const header = sheet.getRange("1:1");
  
  header.setBackground("#1a73e8")
        .setFontColor("#ffffff")
        .setFontWeight("bold")
        .setFontSize(12);
}

Performance Best Practice: Minimize API Calls

Every call to getValues(), setValue(), or similar methods communicates with Google's servers. Reading or writing large blocks of data in a single call is always faster than doing it cell by cell. Follow this pattern:

  1. Read all data you need at once into a JavaScript array.
  2. Process and modify the array in memory.
  3. Write the entire modified array back in one call.

This single habit will make your scripts run significantly faster and avoid hitting quota limits.