Google Workspace Automation

Simplify Your Work with Apps Script

Introduction

Google Apps Script enables automation of repetitive tasks in Google Workspace (formerly G Suite). This guide helps you implement automations for your Google Sheets and Docs.

Benefits

• Reduce manual work
• Maintain consistency
• Save time
• Minimize errors
• Automate repetitive tasks

General Setup Guide

1. Access Apps Script

1. Open your Google Sheet/Doc
2. Click on Extensions in the top menu
3. Select Apps Script

2. Configure Project Settings

1. Initial Setup:

   • Click "Untitled project" at the top
   • Give a meaningful name
   • Click OK/Save

2. Script Settings:

   • Click ⚙️ (Settings) icon
   • Set Time zone
   • Check "Log uncaught exceptions"
   • Check "Enable Chrome V8 runtime"
   • Check "Show manifest file"

3. Add Manifest File

{
  "timeZone": "Asia/Kolkata",
  "exceptionLogging": "CLOUD",
  "runtimeVersion": "V8",
  "dependencies": {},
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/script.projects",
    "https://www.googleapis.com/auth/script.container.ui"
  ],
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "DOMAIN"
  }
}

4. Authorization Steps

1. Save project (Ctrl/Cmd + S)
2. Select test function
3. Click Run
4. Complete Authorization:
   • Review Permissions
   • Choose account
   • Allow access
   • Select all permissions
   • Continue and Allow

Example Project: Resource Plan Auto Generator

Overview

Automates weekly sprint sheet creation in Google Sheets.

Features

• Automatic sheet creation
• Date updates
• Format preservation
• Team structure maintenance
• Weekly scheduling

Setup Instructions

1. Create base sheet named "OX Resource Plan | 2025"
2. Name first tab "Sprint 1"
3. Add following code to Apps Script:

function createNextWeekPlanningSheet() {
  // Get the current spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // Find the last sprint number and sheet
  let lastSprintNumber = 0;
  let templateSheet = null;

  sheets.forEach((sheet) => {
    const sheetName = sheet.getName();
    if (sheetName.startsWith("Sprint ")) {
      const sprintNum = parseInt(sheetName.split(" ")[1]);
      if (!isNaN(sprintNum) && sprintNum > lastSprintNumber) {
        lastSprintNumber = sprintNum;
        templateSheet = sheet;
      }
    }
  });

  // If no sprint exists, use active sheet
  if (!templateSheet) {
    templateSheet = ss.getActiveSheet();
    templateSheet.setName("Sprint 1");
    lastSprintNumber = 1;
  }

  // Create new sprint
  const newSprintNumber = lastSprintNumber + 1;
  const newSheet = templateSheet.copyTo(ss);
  newSheet.setName(`Sprint ${newSprintNumber}`);

  // Position the new sheet
  ss.setActiveSheet(newSheet);
  ss.moveActiveSheet(sheets.length + 1);

  // Get next week's dates
  const nextWeekDates = getNextWeekDates();

  // Clear work cells
  const dataRange = newSheet.getRange("D3:L" + newSheet.getLastRow());
  dataRange.clearContent();

  // Update date headers
  for (let i = 0; i < 5; i++) {
    const startCol = 4 + i * 4;

    // Unmerge if needed
    try {
      newSheet.getRange(1, startCol, 1, 4).breakApart();
    } catch (e) {
      // Ignore if not merged
    }

    // Set date and merge
    newSheet.getRange(1, startCol).setValue(nextWeekDates[i]);
    newSheet.getRange(1, startCol, 1, 4).merge();

    // Update sessions
    for (let j = 0; j < 4; j++) {
      newSheet.getRange(2, startCol + j).setValue(`Session ${j + 1}`);
    }
  }
}

function getNextWeekDates() {
  const today = new Date();
  const nextWeek = [];

  const daysUntilMonday = (8 - today.getDay()) % 7;
  const nextMonday = new Date(today);
  nextMonday.setDate(today.getDate() + daysUntilMonday);

  for (let i = 0; i < 5; i++) {
    const date = new Date(nextMonday);
    date.setDate(nextMonday.getDate() + i);
    nextWeek.push(
      Utilities.formatDate(
        date,
        Session.getScriptTimeZone(),
        "EEEE (dd-MMM-yyyy)"
      )
    );
  }

  return nextWeek;
}

function createTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach((trigger) => ScriptApp.deleteTrigger(trigger));

  ScriptApp.newTrigger("createNextWeekPlanningSheet")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.THURSDAY)
    .atHour(13)
    .create();
}

function testSheetCreation() {
  createNextWeekPlanningSheet();
}

Testing

1. Run testSheetCreation()
2. Verify new sprint creation
3. Check date formats
4. Confirm formatting

Schedule Automation

1. Run createTrigger()
2. Confirms Thursday 1 PM schedule