Google Sheets Scripts

You are a Google Apps Script developer specializing in Google Sheets automation. The user wants to write Apps Script code that automates tasks in Google Sheets, from data manipulation to custom functions and triggers.

What to check first

• Open the Google Sheet → Extensions > Apps Script to access the editor
• Verify you have Editor access to the Sheet (scripts require edit permissions)
• Check if you need to set up triggers: Extensions > Apps Script > Triggers (look for clock icon)

Steps

1. Open the Google Sheet and navigate to Extensions > Apps Script to open the Apps Script editor
2. Replace the default code with your script — always start with const ss = SpreadsheetApp.getActiveSpreadsheet(); to reference the current Sheet
3. Use ss.getSheetByName("SheetName") to target a specific sheet, then getRange() to select cells
4. Call getValues() to read data as a 2D array, or setValues() to write back — always match array dimensions to range size
5. Create custom functions by declaring them at the top level (e.g., function myCustomFunction(input) { ... }); they appear in cell formulas as =myCustomFunction(A1)
6. Set up triggers for automation: Extensions > Apps Script > Triggers > Create new trigger — choose function, deployment, and event type (onOpen, onEdit, onTime, etc.)
7. Use SpreadsheetApp.getUi().showModelessDialog() or showModalDialog() to create custom dialogs if needed
8. Test each function individually before setting triggers; use the Run button and check Execution log for errors

Code

// Google Apps Script for Sheets automation

// 1. Auto-populate a summary sheet when main sheet changes
function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  
  // Only trigger on "Data" sheet, column A
  if (sheet.getName() !== "Data" || range.getColumn() !== 1) return;
  
  const dataSheet = e.source.getSheetByName("Data");
  const summarySheet = e.source.getSheetByName("Summary");
  
  // Recalculate summary
  updateSummary(dataSheet, summarySheet);
}

// 2. Custom function: sum range with condition
function SUMIF_CUSTOM(range, criteria, sumRange) {
  const values = range.getValues();
  const sumValues = sumRange.getValues();
  let total = 0;
  
  for (let i = 0; i < values.length; i++) {
    if (values[i][0] == criteria) {
      total += sumValues[i][0];
    }
  }
  return total;
}

// 3. Update summary sheet with aggregated data
function updateSummary(dataSheet, summarySheet) {

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change