Google AppScript Automation Agent

Google Apps Script lets you automate Gmail, Sheets, Drive, Calendar, and Docs - but requires JavaScript knowledge that most business users lack. A Gemini-powered AppScript generator bridges this gap: users describe what they want in plain English and receive ready-to-paste Apps Script code that works immediately.

In this tutorial, we build a ShopMax India AppScript automation agent. Users describe business tasks like sending weekly sales email reports or formatting inventory sheets, and the agent generates the complete Apps Script code they can paste directly into the Google Apps Script editor.

The below example shows the agent generating two different automation scripts from natural language requests.

It gives the following output,

function sendWeeklySalesReport() {
  // Get the Sales Data sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sales Data");
  var data = sheet.getDataRange().getValues();

  // Calculate current week date range
  var today = new Date();
  var weekStart = new Date(today);
  weekStart.setDate(today.getDate() - today.getDay());

  // Aggregate weekly totals
  var totalRevenue = 0;
  var totalUnits = 0;
  var rows = [];
  for (var i = 1; i < data.length; i++) {
    var rowDate = new Date(data[i][0]);
    if (rowDate >= weekStart && rowDate <= today) {
      totalUnits += data[i][2];
      totalRevenue += data[i][3];
      rows.push(data[i]);
    }
  }

  // Build HTML email body
  var html = "
ShopMax India - Weekly Sales Summary
";
  html += "
Total Revenue: Rs " + totalRevenue.toLocaleString("en-IN") + "
";
  html += "
Total Units Sold: " + totalUnits + "
";
  html += "";
  rows.forEach(function(row) {
    html += "";
  });
  html += "
Date
Product
Units
Revenue
" + row[0] + "
" + row[1] + "
" + row[2] + "
Rs " + row[3] + "
";

  // Send the email
  var subject = "ShopMax Weekly Sales Report - " + Utilities.formatDate(today, "Asia/Kolkata", "dd-MMM-yyyy");
  GmailApp.sendEmail("[email protected]", subject, "", {htmlBody: html});
  Logger.log("Report sent successfully");
}

It gives the following output,

function colourCodeInventory() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory");
  var data = sheet.getDataRange().getValues();
  var lastRow = data.length;

  // Bold the header row
  sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight("bold");

  // Colour-code based on stock level in column C
  for (var i = 1; i < lastRow; i++) {
    var stock = data[i][2]; // Column C (index 2)
    var range = sheet.getRange(i + 1, 1, 1, sheet.getLastColumn());
    if (stock < 10) {
      range.setBackground("#FF4444"); // Red - critical
    } else if (stock <= 25) {
      range.setBackground("#FFD700"); // Yellow - low
    } else {
      range.setBackground("#90EE90"); // Green - healthy
    }
  }
  Logger.log("Inventory colour coding complete");
}

To use the generated code: open script.google.com, create a new project, paste the code, save, and run the function. Grant the necessary permissions when prompted. Set up time-based triggers (Edit > Current project triggers) to run sendWeeklySalesReport automatically every Monday morning. The agent can generate scripts for any Google Workspace automation - Forms, Calendar, Drive, Docs - just describe the task in plain English.