"Every month-end, tallying sales data in Excel, sharing it via email, and generating invoices one by one"—are you spending hours every month on these repetitive tasks?
Google Sheets, included with Google Workspace, is not only as intuitive to use as Excel, but also comes packed with cloud-native automation capabilities. Mastering practical functions and pairing them with GAS (Google Apps Script) can dramatically cut repetitive tasks like tallying, sending notifications, and creating documents.
In this article, we explain formula techniques that SME staff can put into practice immediately, alongside an introduction to GAS automation using concrete code examples.
3 essential functions to master first
VLOOKUP / XLOOKUP: Automatically referencing data from other sheets
Looking up data in a table—such as retrieving a product name and unit price from a product code, or fetching an address from a customer ID—is a routine business task. VLOOKUP is the classic standard for this, and XLOOKUP is its modern evolution.
=VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE)Based on the product code entered in cell A2, this formula retrieves the product name (column 2) from columns A through C of the "Product Master" sheet. Simply typing the code populates the product name automatically, preventing manual entry errors.
ARRAYFORMULA: Applying a single formula across an entire column
Normally, you have to copy formulas down row by row, but using ARRAYFORMULA applies a single formula automatically across an entire column. When new rows are added, there is no need to re-paste formulas, lowering maintenance overhead.
=ARRAYFORMULA(IF(A2:A<>"", B2:B * C2:C, ""))This automatically calculates total amounts in column D by multiplying column B (quantity) by column C (unit price). If you find yourself copying formulas every time new rows are added to order tracking sheets, replacing them with this single formula makes ongoing operations effortless.
QUERY: SQL-like data aggregation
When you want to filter and aggregate data based on multiple conditions, the QUERY function is exceptionally powerful. It lets you write SQL-like queries directly inside Google Sheets.
=QUERY(売上データ!A:E, "SELECT B, SUM(E) WHERE D='東京' GROUP BY B LABEL SUM(E) '売上合計'", 1)This automatically calculates total sales for each sales representative in "Tokyo" onto a separate sheet, eliminating the monthly hassle of refreshing pivot tables.
Full automation with GAS (Google Apps Script)
To handle workflows that formulas alone cannot manage—such as sending emails, scheduling recurring tasks, and generating documents automatically—GAS comes into play. GAS is a JavaScript-based scripting language that you can start using simply by selecting "Extensions → Apps Script" from the Google Sheets menu.
Case 1: Automated daily morning sales summary emails
This script automatically sends summary figures from a sales sheet to team members via email every morning at 9:00 AM.
function sendDailySalesReport() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('売上データ');
const total = sheet.getRange('F2').getValue(); // 当日売上合計セル
const today = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');
GmailApp.sendEmail(
'manager@example.com',
`【売上報告】${today}`,
`本日の売上合計: ¥${total.toLocaleString()}`
);
}Once you write this script, simply set a trigger in Apps Script for the getDailySalesReport function to "Time-driven → Day timer → 9am to 10am." The email will arrive automatically every morning.
Case 2: Auto-generating invoices from form responses
By integrating with Google Forms, you can also generate invoice documents automatically based on submitted form responses. Below is a simple example that merges data from the latest row of a Google Sheet into a Google Docs template.
function createInvoiceFromSheet() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('受注一覧');
const lastRow = sheet.getLastRow();
const data = sheet.getRange(lastRow, 1, 1, 5).getValues()[0];
const [date, clientName, item, qty, amount] = data;
const templateId = 'YOUR_TEMPLATE_DOC_ID'; // テンプレートのドキュメントID
const newDoc = DriveApp.getFileById(templateId).makeCopy(`請求書_${clientName}_${date}`);
const body = DocumentApp.openById(newDoc.getId()).getBody();
body.replaceText('{{顧客名}}', clientName);
body.replaceText('{{品目}}', item);
body.replaceText('{{数量}}', qty);
body.replaceText('{{金額}}', `¥${amount.toLocaleString()}`);
}By preparing a Google Docs template containing placeholders like {{顧客名}}, a merged copy is automatically generated each time the script runs. Month-end billing workflows can be completed in a single click.
Benchmark implementation benefits
Companies that automated processes by pairing GAS with Google Sheets have reported reducing time spent on month-end invoicing from 10 hours to under 2 hours (an approximate 80% reduction). In order management automation, results showed a 60% reduction in order processing time alongside a dramatic drop in human errors.
Even without prior programming experience, you can begin by copying and pasting standard scripts like those introduced here. Start by automating one small repetitive task with GAS to experience the benefits firsthand.
Getting even more out of Google Workspace
Combining Google Sheets and GAS is only a fraction of the automation potential Google Workspace offers. In Benefits of Adopting Google Workspace, we examine operational improvements across the entire suite, including Gmail, Drive, Meet, and Calendar. Deepening integrations between tools unlocks even greater efficiencies.
If you want to build custom GAS scripts tailored to your company's workflows but lack in-house development resources, or if you prefer having professionals manage initial Google Workspace setup and rollout, feel free to consult GleamHub. We provide tailored support for SMEs, ranging from Google Workspace rollout assistance to developing custom workflow automation scripts.
