Google Sheets and Apps Script: No Programming Experience Needed

You've got a spreadsheet with 500 rows of data. Every time a new record arrives, you should send an email to accounting. You do it by hand. That's two hours wasted each week.

Or you're running a Google Form collecting requests. You want Slack to notify you instantly when an important response comes through. Instead, you're checking the Form every morning.

Solve both in 10 minutes with Apps Script.

Apps Script is Google's automation language. No servers. No infrastructure. No cost. It's JavaScript that lives in Google Drive and can touch Sheets, Gmail, Docs, Forms, Calendar, everything.

But "programming" sounds intimidating. It's not. In this chapter 4 of the Google Sheets series, we'll go from "Hello World" to production scripts you can copy, paste, and run immediately.

What is Apps Script, really?

Apps Script is JavaScript that runs on Google's servers. You don't need your own server. You don't pay for hosting. You write the logic, upload it to Google Drive, and it executes on demand or on a schedule.

Think of it as Excel macros, but with superpowers. And free.

The difference from QUERY, ARRAYFORMULA, LAMBDA

In chapter 3 we covered formulas: QUERY filters data, ARRAYFORMULA spreads formulas across ranges, LAMBDA creates custom functions. These all happen inside the sheet.

Apps Script is different. It's not a formula. It's a programme. It runs outside the sheet. It can read data, process it, send emails, create files, call external APIs, fire webhooks. Formulas can't do that.

When you use formulas: display data, calculate values, transform ranges.

When you use Apps Script: automate actions, integrate with other services, run complex logic based on conditions.

Getting started: where to write code

In Google Sheets, open any sheet. Go to Extensions > Apps Script.

A new tab opens. You'll see an editor with sample code:

function myFunction() {
  Logger.log('Hello, world!');
}

This is your sandbox. Write here. Test here. Run here.

Note: it doesn't matter if the sheet is shared or private. The code is yours. Execution permissions are yours.

Script 1: Log every change to an audit sheet

Scenario: you have an invoices sheet. You want to track who changed what and when. Manual copying doesn't work. This does.

The complete code

function onEdit(e) {
  var sheet = e.source.getSheetByName("Invoices");
  var logSheet = e.source.getSheetByName("Log");
  
  if (!sheet || !logSheet) return;
  
  var range = e.range;
  var user = Session.getActiveUser().getEmail();
  var timestamp = new Date();
  var oldValue = e.oldValue || "";
  var newValue = e.value || "";
  var cellAddress = range.getA1Notation();
  
  logSheet.appendRow([
    timestamp,
    user,
    cellAddress,
    oldValue,
    newValue,
    "Change in Invoices sheet"
  ]);
}

How it works, line by line

• function onEdit(e) – Special trigger. Runs every time you edit a cell. The e object contains all the change details.

• var sheet = e.source.getSheetByName("Invoices") – Gets the sheet named "Invoices". If it doesn't exist, sheet will be null.

• var logSheet = e.source.getSheetByName("Log") – Gets the log sheet where we record changes.

• if (!sheet || !logSheet) return; – If either sheet is missing, exit. Do nothing.

• var range = e.range – The cell you edited.

• var user = Session.getActiveUser().getEmail() – Email of whoever made the change. Apps Script knows who's running it.

• var timestamp = new Date() – Exact timestamp of the change.

• var oldValue = e.oldValue || "" – The previous value. If the cell was empty, use an empty string.

• var newValue = e.value || "" – The value you just typed.

• var cellAddress = range.getA1Notation() – Cell reference. Example: "A5", "B10:D20".

• logSheet.appendRow([...]) – Adds a new row at the end of the log sheet with all this data.

Steps to use this code

1. In the same sheet, create a new tab called "Log" (Extensions > Apps Script > Editor).

2. Copy the code above. Paste it into the editor (replace the sample function).

3. Click the clock icon (Triggers). Or go to Triggers in the top menu.

4. Click Create new trigger.

5. Configure:

   • Function: onEdit
   • Deployment: Head
   • Event source: From spreadsheet
   • Event type: On edit
   • Notification level: Show notification only on error

6. Click Save. Google will ask for permissions the first time.

7. A dialogue appears: "Apps Script needs authorisation". Click your account, then Allow.

What happens next

Every time you edit a cell in the "Invoices" tab, it's automatically logged in the "Log" tab. You'll see:

Perfect for audit trails.

onEdit limitations

The onEdit trigger runs every time you edit, even if you edit programmatically from another script. This can cause infinite loops if you're not careful.

Solution: use onEdit only for manual changes. If you need scripts that modify data without a trigger, use onOpen or run functions manually.

Script 2: Send invoices by email automatically

Scenario: you have a sheet with pending invoices. You want to read them, generate PDFs, and email them with a single click.

This one's more complex because it reads data, generates documents, and integrates with Gmail.

The complete code

function sendInvoicesByEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange("A2:F100").getValues(); // Read up to 100 rows
  var folder = DriveApp.getFolderById("FOLDER_ID_HERE"); // Your Drive folder
  
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var invoiceId = row[0];
    var clientName = row[1];
    var email = row[2];
    var amount = row[3];
    var status = row[4];
    var dueDate = row[5];
    
    // Only process pending invoices
    if (status !== "Pending") continue;
    
    // Create a unique name for the PDF
    var pdfName = "Invoice_" + invoiceId + ".pdf";
    
    // Check if the PDF already exists
    var files = folder.getFilesByName(pdfName);
    var pdfFile;
    
    if (files.hasNext()) {
      pdfFile = files.next();
    } else {
      // If not, create it
      var docTemplate = DocumentApp.create("Temp_Invoice_" + invoiceId);
      var body = docTemplate.getBody();
      body.clear();
      body.appendParagraph("INVOICE #" + invoiceId)
        .setHeading(DocumentApps.ParagraphHeading.HEADING1);
      body.appendParagraph("Client: " + clientName);
      body.appendParagraph("Amount: " + amount + " EUR");
      body.appendParagraph("Due: " + dueDate);
      body.appendParagraph("---");
      body.appendParagraph("Please pay by the date shown above.");
      
      docTemplate.saveAndClose();
      
      // Convert to PDF
      var docId = docTemplate.getId();
      var pdfBlob = DriveApp.getFileById(docId).getAs("application/pdf");
      pdfBlob.setName(pdfName);
      pdfFile = folder.createFile(pdfBlob);
      
      // Delete the temporary document
      DriveApp.getFileById(docId).setTrashed(true);
    }
    
    // Send email with the PDF
    GmailApp.sendEmail(
      email,
      "Invoice " + invoiceId + " - Payment required",
      "Dear " + clientName + ",\n\nPlease find attached invoice " + invoiceId + 
      " due on " + dueDate + ".\n\n" +
      "Amount: " + amount + " EUR\n\n" +
      "We appreciate prompt payment confirmation.\n\n" +
      "Best regards,\nBilling Team",
      { attachments: [pdfFile] }
    );
    
    // Mark the invoice as "Sent" in the sheet
    sheet.getRange(i + 2, 5).setValue("Sent");
    
    Logger.log("Invoice " + invoiceId + " sent to " + email);
  }
}

Explanation, line by line

• var sheet = SpreadsheetApp.getActiveSheet() – Gets the active sheet.

• var data = sheet.getRange("A2:F100").getValues() – Reads cells A2 to F100 as an array. getValues() returns a two-dimensional array.

• var folder = DriveApp.getFolderById("FOLDER_ID_HERE") – Accesses a folder in Drive. You need the folder ID (find it in the Drive URL: /folders/FOLDER_ID_HERE).

• The loop for (var i = 0; i < data.length; i++) – Iterates over each row.

• var row = data[i] – Each row is an array: [invoiceId, clientName, email, amount, status, dueDate].

• if (status !== "Pending") continue – Skip rows that aren't "Pending". This prevents double sends.

• var files = folder.getFilesByName(pdfName) – Check if the PDF already exists in the folder.

• if (files.hasNext()) – If it exists, use it. If not, create a new one.

• DocumentApp.create(...) – Creates a document in Drive (temporary).

• body.appendParagraph(...) – Adds paragraphs to the document. This is basic; you could enhance with styles, tables, etc.

• DriveApp.getFileById(docId).getAs("application/pdf") – Converts the document to PDF (blob).

• folder.createFile(pdfBlob) – Saves the PDF to the specified folder.

• GmailApp.sendEmail(...) – Sends an email. The attachments object attaches files.

• sheet.getRange(i + 2, 5).setValue("Sent") – Mark the row as "Sent" (i + 2 because data starts at row 2).

Steps to use this script

1. Go to Extensions > Apps Script.

2. Copy the code above.

3. In the sheet, create columns with headers:

   • A: Invoice ID
   • B: Client
   • C: Email
   • D: Amount
   • E: Status (values: "Pending", "Sent", "Paid")
   • F: Due Date

4. Get your Drive folder ID: copy the folder URL, extract the ID between /folders/ and the end.

5. Replace FOLDER_ID_HERE in the script with your real ID.

6. Open the Apps Script editor. Find the clock icon and create a new trigger or run manually from Extensions > Apps Script > Run function > sendInvoicesByEmail.

7. Grant permissions if prompted.

Permission authorisation

Apps Script needs permission for:

• Reading the sheet (SpreadsheetApp).
• Accessing Drive (DriveApp).
• Creating documents (DocumentApp).
• Sending emails (GmailApp).

The first run will ask for authentication. Google shows which permissions are needed. Click Allow if you trust it.

Note: anyone with access to the sheet can also run this script using your permissions.

Script 3: Fire a webhook to Slack/Discord when a Google Form response arrives

Scenario: you're running a Google Form for requests. You want each response to fire a webhook to Slack and alert the team.

This script integrates with external APIs.

The complete code

function onFormSubmit(e) {
  var form = FormApp.getActiveForm();
  var response = e.response;
  var itemResponses = response.getItemResponses();
  
  var slackWebhookUrl = "https://hooks.slack.com/services/YOUR_WEBHOOK_URL_HERE";
  var discordWebhookUrl = "https://discord.com/api/webhooks/YOUR_WEBHOOK_URL_HERE";
  
  var message = "New form response:\n\n";
  
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var question = itemResponse.getItem().getTitle();
    var answer = itemResponse.getResponse();
    message += "*" + question + "* → " + answer + "\n";
  }
  
  // Send to Slack
  var payloadSlack = {
    text: message,
    username: "Google Forms Bot",
    icon_emoji: ":incoming_envelope:"
  };
  
  var optionsSlack = {
    method: "post",
    payload: JSON.stringify(payloadSlack),
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(slackWebhookUrl, optionsSlack);
  
  // Send to Discord (different format)
  var payloadDiscord = {
    content: message,
    username: "Google Forms Bot",
    avatar_url: "https://www.gstatic.com/images/branding/product/1x/forms_64dp.png"
  };
  
  var optionsDiscord = {
    method: "post",
    payload: JSON.stringify(payloadDiscord),
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(discordWebhookUrl, optionsDiscord);
  
  Logger.log("Response sent to Slack and Discord");
}

Explanation, line by line

• function onFormSubmit(e) – Special trigger that fires every time someone submits the form. The e object holds the response.

• var form = FormApp.getActiveForm() – Gets the form.

• var response = e.response – The submitted response.

• var itemResponses = response.getItemResponses() – Array of each question and its answer.

• Slack and Discord webhooks are public URLs that accept POST data. You get them from your workspace settings.

• The loop iterates over each question and builds a plain-text message with question/answer pairs.

• var payloadSlack = {...} – The data structure Slack expects.

• JSON.stringify(payloadSlack) – Converts the JavaScript object to JSON.

• UrlFetchApp.fetch(slackWebhookUrl, optionsSlack) – Makes a POST request to the Slack webhook.

• muteHttpExceptions: true – If the request errors, don't throw an exception, just continue.

• payloadDiscord uses a different format because Discord has a different webhook structure.

• Both requests execute (you can send to multiple places).

Steps to use this script

1. Open your Slack workspace (or Discord).

2. For Slack: Go to api.slack.com > Apps > Create New App > From scratch. Name: "Google Forms Bot". Workspace: yours. Create.

3. In the app, go to "Incoming Webhooks", enable it. Create a new webhook. Choose the channel where it should post (e.g. #forms).

4. Copy the Slack webhook URL. Paste it in the script: https://hooks.slack.com/services/YOUR_WEBHOOK_URL_HERE.

5. For Discord (optional): Go to your Discord server > Channel Settings > Integrations > Webhooks > New Webhook. Name: "Google Forms". Copy the URL.

6. Paste the URL in the script: https://discord.com/api/webhooks/YOUR_WEBHOOK_URL_HERE.

7. Go to your Google Form. Go to Extensions > Apps Script > Editor. Paste the code.

8. Create a new trigger:

   • Function: onFormSubmit
   • Event source: From form
   • Event type: On form submit

9. Save. Authorise.

10. Submit a test response in the form. You'll see the alert in Slack/Discord instantly.

Security considerations

Webhook URLs are public. Anyone with the URL can send fake messages.

For production, consider:

• Rotating the webhook URL periodically.
• Validating that the message genuinely comes from Google (using tokens).
• Using Apps Script as a proxy instead of direct webhooks.

To start, this setup is fine. Just don't share the URLs publicly.

When NOT to use Apps Script

Apps Script is powerful, but it has limits.

Don't use it if:

• You need to process millions of records. Apps Script has execution limits (6 minutes per run).
• You need a complex database. Use Firestore, PostgreSQL, or another database.
• You need business logic that's highly intricate. Move to your own backend (Node.js, Python, etc.).
• You need heavy calculations in real time. Apps Script isn't high-performance compute.

Use it if:

• You need glue code connecting Google Workspace to other services.
• You need automations that run occasionally (not 1000 times per second).
• You need audit logs, notifications, data exports.
• You need simple but repetitive logic.

Common errors and how to fix them

Error: "You don't have permission to access this sheet"

• Cause: you're running the script with an account that doesn't have access.
• Fix: use the account that owns the sheet, or share the sheet with whoever will run it.

Error: "onEdit runs in an infinite loop"

• Cause: your script edits the sheet, which triggers onEdit again.
• Fix: use a condition (if (sheet.getName() === "Data Input") ...) to run only on specific tabs.

Error: "getRangeByName returns null"

• Cause: the named range doesn't exist.
• Fix: go to Data > Named ranges (in Sheets) and check the name.

Error: "Execution time exceeded"

• Cause: the script takes longer than 6 minutes.
• Fix: split it into smaller scripts, use scheduled triggers instead of long loops.

Webhook receives no data

• Cause: wrong URL, webhook is inactive, incorrect payload format.
• Fix: test the URL manually with curl, check the service dashboard (Slack, Discord) for incoming requests.

Next steps: where to learn more

These three scripts cover 80% of real-world use cases. If you need more:

• Official docs: developers.google.com/apps-script
• API explorer: open Extensions > Apps Script > Editor > + (plus icon) > Libraries. Search "Google Apps Script samples".
• Community: Stack Overflow, tag google-apps-script.

Apps Script isn't intimidating programming. It's automation scripting. With these three patterns, you can solve almost any repetitive workflow in Google Workspace.

If you'd like to audit your current automation stack and identify where you could save hours each week, book your 30-minute session.

Or if you prefer structured learning, check out our Google Sheets courses with step-by-step exercises, including live Apps Script practicals.