Google Apps Script Snippets to Supercharge Your Workflow

Feeling bogged down by repetitive tasks in Google Sheets and Forms? Look no further than Google Apps Script! This powerful scripting language lets you automate tasks and customize your Google Workspace experience. But where do you begin?

This blog post offers a taste of what Apps Script can do, with five handy code snippets to streamline your workflow:

Boost Spreadsheet Efficiency:

Effortless Data Entry: Tired of manually filling dates in your spreadsheets? This snippet automates the process, filling a column with sequential dates starting from a chosen point.

function autoFillDates(sheet) {
  const startDate = new Date("2024-03-03");  // Replace with your start date
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1);
  range.setValues(range.generateValues().map(row => [startDate.addDays(row[0] - 1)]));
}

function autoFillDates(sheet) {

const startDate = new Date("2024-03-03"); // Replace with your start date

const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1);

range.setValues(range.generateValues().map(row => [startDate.addDays(row[0] - 1)]));

}

Smarter Spreadsheets with Conditional Formatting: Want to quickly identify critical data points in your spreadsheets? This snippet applies conditional formatting based on values in a specific column. Values exceeding a threshold turn green, while those falling below another threshold turn red, providing an instant visual cue.

function colorCodeStatus(sheet) {
  const range = sheet.getRange(1, 2, sheet.getLastRow(), 1);
  const rules = [
    { condition: SpreadsheetApp.newConditionValue('IS_GREATER_THAN', 90), format: { backgroundColor: '#00FF00' } },
    { condition: SpreadsheetApp.newConditionValue('IS_LESS_THAN', 70), format: { backgroundColor: '#FF0000' } }
  ];
  range.setConditionalFormatRules(rules);
}

function colorCodeStatus(sheet) {

const range = sheet.getRange(1, 2, sheet.getLastRow(), 1);

const rules = [

{ condition: SpreadsheetApp.newConditionValue('IS_GREATER_THAN', 90), format: { backgroundColor: '#00FF00' } },

{ condition: SpreadsheetApp.newConditionValue('IS_LESS_THAN', 70), format: { backgroundColor: '#FF0000' } }

];

range.setConditionalFormatRules(rules);

}

Clean Up Your Spreadsheets: Data can get messy over time. This snippet helps you identify and remove duplicate rows within a sheet, keeping your data organized.

function removeDuplicates(sheet) {
  const dataRange = sheet.getRange(1, 1, sheet.getLastRow());
  const values = dataRange.getValues();
  const uniqueValues = values.filter((row, index) => values.findIndex(r => r.join('') === row.join('')) === index);
  sheet.clearContents();
  sheet.getRange(1, 1, uniqueValues.length, uniqueValues[0].length).setValues(uniqueValues);
}

function removeDuplicates(sheet) {

const dataRange = sheet.getRange(1, 1, sheet.getLastRow());

const values = dataRange.getValues();

const uniqueValues = values.filter((row, index) => values.findIndex(r => r.join('') === row.join('')) === index);

sheet.clearContents();

sheet.getRange(1, 1, uniqueValues.length, uniqueValues[0].length).setValues(uniqueValues);

}

Form Automation:

Pre-populate Forms Based on Selections: Simplify form filling by automatically populating fields based on user selections. This snippet retrieves a selected item from a form submission and uses it to populate a corresponding spreadsheet. You can expand this logic to populate other fields based on the selection.

function onFormSubmit(e) {
  const item = e.namedValues['Item'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRow = sheet.appendRow([item]);
  // Additional logic to populate other fields based on 'item' selection
}

function onFormSubmit(e) {

const item = e.namedValues['Item'];

const sheet = SpreadsheetApp.getActiveSheet();

const dataRow = sheet.appendRow([item]);

// Additional logic to populate other fields based on 'item' selection

}

Email Automation:

Send Automated Emails Based on Spreadsheet Data: No more manual emails! This snippet iterates through a spreadsheet and sends emails to addresses in a specific column with a predefined message.

function sendEmails(sheet) {
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  for (const row of values) {
    const email = row[0];  // Assuming email address is in first column
    const message = "Your order is confirmed!";
    MailApp.sendEmail(email, "Order Confirmation", message);
  }
}

function sendEmails(sheet) {

const dataRange = sheet.getDataRange();

const values = dataRange.getValues();

for (const row of values) {

const email = row[0]; // Assuming email address is in first column

const message = "Your order is confirmed!";

MailApp.sendEmail(email, "Order Confirmation", message);

}

These are just the tip of the iceberg! Explore the vast potential of Google Apps Script by diving into the official documentation https://www.google.com/script/start/ to find even more code snippets and unleash the full power of automation within your Google Workspace.

Leave a Reply Cancel reply