App Script References

2023-04-06| Reading Time: 6 | Words: 1058

Google Apps Script - Powerful Ecosystem Integration

Google Apps Script is an incredible platform that extends the Google ecosystem's capabilities. Here are some powerful use cases and favorite tricks for leveraging this technology.

Official Resources

Favorite Apps Script Tricks

1. Document-to-Email Reporting System

Transform Google Docs reports into automated email reports with embedded documents. This approach enables:

Automatic report generation from template documents
Dynamic content insertion from other Google services
Scheduled email delivery with rich formatting
Embedded charts, tables, and other document elements

function sendDocumentReport() {
  // Get template document
  const templateId = 'YOUR_TEMPLATE_DOC_ID';
  const template = DriveApp.getFileById(templateId);
  
  // Create copy for this report
  const reportCopy = template.makeCopy(`Report_${new Date().toISOString().split('T')[0]}`);
  const doc = DocumentApp.openById(reportCopy.getId());
  
  // Replace placeholders with dynamic data
  const body = doc.getBody();
  body.replaceText('{{DATE}}', new Date().toLocaleDateString());
  body.replaceText('{{METRIC1}}', getMetricFromSheets());
  
  // Convert to PDF and email
  const pdf = DriveApp.getFileById(reportCopy.getId()).getAs('application/pdf');
  
  GmailApp.sendEmail(
    'recipient@example.com',
    'Weekly Report',
    'Please find attached this week\'s report.',
    {
      attachments: [pdf],
      htmlBody: `<p>Report generated automatically on ${new Date()}</p>`
    }
  );
  
  // Clean up temporary file
  DriveApp.getFileById(reportCopy.getId()).setTrashed(true);
}

function getMetricFromSheets() {
  const sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getActiveSheet();
  return sheet.getRange('B2').getValue();
}

2. OR-Tools Integration with Colaboratory + Google Sheets

Create powerful recalculation workflows by combining:

Google Colaboratory: For complex optimization algorithms using OR-Tools
Google Sheets: As the data interface for non-technical users
Apps Script: As the bridge connecting both platforms
Result: Advanced optimization capabilities accessible through familiar spreadsheet interfaces

function runOptimizationPipeline() {
  const sheetId = 'YOUR_SHEET_ID';
  const sheet = SpreadsheetApp.openById(sheetId).getSheetByName('OptimizationData');
  
  // Get input data from sheets
  const data = sheet.getDataRange().getValues();
  const inputData = {
    constraints: data.slice(1).map(row => ({
      resource: row[0],
      capacity: row[1],
      demand: row[2]
    }))
  };
  
  // Call Colaboratory notebook via HTTP
  const colabUrl = 'YOUR_COLAB_WEBHOOK_URL';
  const response = UrlFetchApp.fetch(colabUrl, {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify(inputData)
  });
  
  const results = JSON.parse(response.getContentText());
  
  // Write results back to sheets
  const resultSheet = SpreadsheetApp.openById(sheetId).getSheetByName('Results');
  resultSheet.clear();
  resultSheet.getRange(1, 1, 1, 3).setValues([['Resource', 'Allocated', 'Efficiency']]);
  
  results.allocation.forEach((item, index) => {
    resultSheet.getRange(index + 2, 1, 1, 3).setValues([[
      item.resource,
      item.allocated,
      item.efficiency
    ]]);
  });
  
  // Create visualization
  createOptimizationChart(resultSheet);
}

function createOptimizationChart(sheet) {
  const chart = sheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(sheet.getRange('A1:C10'))
    .setPosition(5, 5, 0, 0)
    .setOption('title', 'Optimization Results')
    .build();
  
  sheet.insertChart(chart);
}

3. BigQuery + Google Sheets Workflow

Build simple but powerful data pipelines for non-technical personnel:

Connect BigQuery datasets directly to Google Sheets
Automate data refresh schedules
Create custom data transformation functions
Enable self-service analytics for business users without SQL knowledge

function refreshBigQueryData() {
  const projectId = 'your-project-id';
  const query = `
    SELECT 
      date,
      revenue,
      users,
      conversion_rate
    FROM \`your-project.analytics.daily_metrics\`
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    ORDER BY date DESC
  `;
  
  // Execute BigQuery
  const request = BigQuery.newQueryRequest();
  request.query = query;
  request.useLegacySql = false;
  
  const queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;
  
  // Wait for completion and get results
  let queryComplete = false;
  while (!queryComplete) {
    const jobStatus = BigQuery.Jobs.get(projectId, jobId);
    queryComplete = jobStatus.status.state === 'DONE';
    if (!queryComplete) {
      Utilities.sleep(1000);
    }
  }
  
  const rows = BigQuery.Jobs.getQueryResults(projectId, jobId).rows;
  
  // Transform data for sheets
  const sheetData = [['Date', 'Revenue', 'Users', 'Conversion Rate']];
  rows.forEach(row => {
    sheetData.push([
      new Date(row.f[0].v),
      parseFloat(row.f[1].v),
      parseInt(row.f[2].v),
      parseFloat(row.f[3].v)
    ]);
  });
  
  // Update Google Sheets
  const sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('Analytics');
  sheet.clear();
  sheet.getRange(1, 1, sheetData.length, sheetData[0].length).setValues(sheetData);
  
  // Add formatting
  sheet.getRange(1, 1, 1, 4).setFontWeight('bold');
  sheet.getRange(2, 2, sheetData.length - 1, 1).setNumberFormat('$#,##0.00');
  sheet.getRange(2, 4, sheetData.length - 1, 1).setNumberFormat('0.00%');
  
  // Log update
  console.log(`Data refreshed: ${sheetData.length - 1} rows updated`);
}

// Set up trigger for automatic refresh
function createDataRefreshTrigger() {
  ScriptApp.newTrigger('refreshBigQueryData')
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();
}

4. Automated File Backup System

While Google Docs has excellent version control, sometimes you need additional backup strategies:

Weekly snapshots: Create dated backup copies of critical documents
Rapid rollback: Quick restoration to specific points in time
Cross-folder organization: Systematic backup folder structures
Automated cleanup: Remove old backups based on retention policies

function createWeeklyBackup() {
  const sourceFolder = DriveApp.getFolderById('YOUR_SOURCE_FOLDER_ID');
  const backupRootFolder = DriveApp.getFolderById('YOUR_BACKUP_FOLDER_ID');
  
  // Create weekly backup folder
  const today = new Date();
  const weekLabel = `Week_${today.getFullYear()}_${getWeekNumber(today)}`;
  
  let weeklyFolder;
  try {
    weeklyFolder = backupRootFolder.getFoldersByName(weekLabel).next();
  } catch (e) {
    weeklyFolder = backupRootFolder.createFolder(weekLabel);
  }
  
  // Backup all files in source folder
  const files = sourceFolder.getFiles();
  const backupLog = [];
  
  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    const timestamp = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd_HH-mm');
    const backupName = `${fileName}_backup_${timestamp}`;
    
    try {
      const backupFile = file.makeCopy(backupName, weeklyFolder);
      backupLog.push({
        original: fileName,
        backup: backupName,
        size: file.getSize(),
        status: 'SUCCESS'
      });
    } catch (error) {
      backupLog.push({
        original: fileName,
        backup: backupName,
        error: error.toString(),
        status: 'FAILED'
      });
    }
  }
  
  // Create backup report
  createBackupReport(weeklyFolder, backupLog);
  
  // Cleanup old backups (keep last 8 weeks)
  cleanupOldBackups(backupRootFolder, 8);
  
  return backupLog;
}

function getWeekNumber(date) {
  const onejan = new Date(date.getFullYear(), 0, 1);
  return Math.ceil((((date - onejan) / 86400000) + onejan.getDay() + 1) / 7);
}

function createBackupReport(folder, log) {
  const reportName = `Backup_Report_${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd')}`;
  const doc = DocumentApp.create(reportName);
  
  const body = doc.getBody();
  body.appendParagraph('Backup Report').setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph(`Generated: ${new Date()}`);
  body.appendParagraph(`Total files processed: ${log.length}`);
  body.appendParagraph(`Successful backups: ${log.filter(item => item.status === 'SUCCESS').length}`);
  body.appendParagraph(`Failed backups: ${log.filter(item => item.status === 'FAILED').length}`);
  
  log.forEach(item => {
    body.appendParagraph(`${item.status}: ${item.original} → ${item.backup}`);
  });
  
  DriveApp.getFileById(doc.getId()).moveTo(folder);
}

function cleanupOldBackups(backupFolder, weeksToKeep) {
  const folders = backupFolder.getFolders();
  const folderList = [];
  
  while (folders.hasNext()) {
    const folder = folders.next();
    if (folder.getName().startsWith('Week_')) {
      folderList.push({
        folder: folder,
        created: folder.getDateCreated()
      });
    }
  }
  
  // Sort by creation date and remove oldest
  folderList.sort((a, b) => b.created - a.created);
  
  if (folderList.length > weeksToKeep) {
    const foldersToDelete = folderList.slice(weeksToKeep);
    foldersToDelete.forEach(item => {
      console.log(`Deleting old backup folder: ${item.folder.getName()}`);
      item.folder.setTrashed(true);
    });
  }
}

// Set up trigger for weekly backup
function createBackupTrigger() {
  ScriptApp.newTrigger('createWeeklyBackup')
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.SUNDAY)
    .atHour(2)
    .create();
}

Why Apps Script Excels

Ecosystem Integration

Apps Script's strength lies in its seamless integration with the entire Google Workspace:

Native access to Gmail, Drive, Sheets, Docs, Forms, Calendar
No authentication complexity for Google services
Instant deployment and scaling

Non-Technical User Empowerment

The platform enables creating sophisticated workflows that non-technical users can:

Understand and modify
Execute independently
Customize for their specific needs

Rapid Prototyping

Perfect for quick automation solutions that would require significant infrastructure in other platforms.

Best Practices

Start Simple: Begin with basic automations and gradually add complexity
Error Handling: Implement robust error handling for production workflows
User Interface: Create simple HTML interfaces for complex scripts
Documentation: Document scripts well for team collaboration
Testing: Use Apps Script's debugging tools extensively

Apps Script transforms Google Workspace from a productivity suite into a powerful platform for custom business solutions.

Google Apps Script - Powerful Ecosystem Integration

- Official Resources

- Favorite Apps Script Tricks

- Why Apps Script Excels

- Best Practices