Passer au contenu principal
Back to all apps
Google Apps Script

Google Apps Script

Find Google Workspace tips and tricks organised by each app.

6 tips found
Intermediate Make your Sheets scripts 50x faster with batch getValues() / setValues()

Never call getRange().getValue() or setValue() inside a loop: read and write a 2D array in a single call.

Each call to SpreadsheetApp takes 0.1–0.3 seconds. Over 500 rows, calling it inside a loop hits the 6-minute execution limit!

- ✅ Best practice: read the entire range once with const data = sheet.getDataRange().getValues();, process the 2D array in memory, and write back in one call with sheet.getRange(1, 1, data.length, data[0].length).setValues(data);.
- See full templates in our Google Apps Script guide.
Intermediate Store API keys (Gemini, Webhooks, CRM) securely with PropertiesService

Never hardcode an API key in plain text inside a shared Code.gs file.

In the Google Apps Script editor:

1. Click Project Settings (gear icon on the left) › scroll down to Script Properties › click Add script property (e.g. GEMINI_API_KEY).
2. Read it in your code via:
const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
3. For per-user credentials in an add-on, use PropertiesService.getUserProperties().
Beginner Prevent duplicate time-driven triggers from sending emails multiple times

Always delete existing triggers for the same handler function before creating a new one.

Before creating an automated trigger with ScriptApp.newTrigger('myFunction'), clean up existing ones:

ScriptApp.getProjectTriggers().forEach(t => { if (t.getHandlerFunction() === 'myFunction') ScriptApp.deleteTrigger(t); });

This guarantees only one daily or hourly trigger runs, even if a user clicks your setup menu multiple times.
Beginner Manage Script Configurations with ScriptProperties

Easily store and access configuration settings for your Google Apps Script scripts without modifying the code, using the ScriptProperties service.

Instead of "hardcoding" values like sheet IDs, admin email addresses, or thresholds, use the PropertiesService. This makes it easier to maintain and deploy your scripts in different environments.

Set a property:
function setMyConfig() {
  const scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('ADMIN_EMAIL', 'admin@example.com');
  scriptProperties.setProperty('SHEET_ID_DATA', '1ABC...XYZ');
  Logger.log('Properties set.');
}

Get a property:
function getMyConfig() {
  const scriptProperties = PropertiesService.getScriptProperties();
  const adminEmail = scriptProperties.getProperty('ADMIN_EMAIL');
  const sheetId = scriptProperties.getProperty('SHEET_ID_DATA');
  Logger.log('Admin Email: ' + adminEmail);
  Logger.log('Sheet ID: ' + sheetId);
}

You can also manage these properties via the Apps Script user interface: open your script, click the gear icon "Project Settings", then scroll down to "Script properties". You can add, edit, or delete properties manually there.
Manage Script Configurations with ScriptProperties
Intermediate Implement Robust Error Handling with Notifications

Ensure the reliability of your Apps Script scripts by implementing error handling with automatic email notifications in case of issues.

For autonomously running scripts (triggers, Web Apps), effective error handling is vital. Use try...catch blocks and send email notifications to be alerted immediately.

Example:
function myMonitoredFunction() {
  try {
    // Your code that could potentially fail
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
    if (!sheet) {
      throw new Error('The sheet "Data" was not found.');
    }
    // ... rest of your logic ...
  } catch (e) {
    const scriptName = ScriptApp.getProjectKey();
    const functionName = arguments.callee.name; // Or simply 'myMonitoredFunction'
    const adminEmail = 'your.email@example.com'; // Or stored in ScriptProperties

    const subject = `[GAS ERROR] ${scriptName} - ${functionName}`; 
    const body = `An error occurred in script "${scriptName}", function "${functionName}" :

Message : ${e.message}
Name : ${e.name}
File : ${e.fileName}
Line : ${e.lineNumber}
Stack : ${e.stack}`;

    MailApp.sendEmail(adminEmail, subject, body);
    Logger.log(body); // For execution log
  }
}

Remember to authorize the script to send emails on first execution.
Implement Robust Error Handling with Notifications
Intermediate Optimize Performance with Batch Operations

Drastically reduce the execution time of your Google Apps Script scripts by using batch operations to interact with Google services.

When manipulating data in Google Sheets, Docs, or other services, it's crucial to avoid repeated individual API calls. Each call has latency. Instead, use batch operations.

Bad practice (slow):
function slowUpdate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  for (let i = 1; i <= 100; i++) {
    sheet.getRange(i, 1).setValue('Value ' + i);
  }
}

Good practice (fast):
function fastUpdate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = [];
  for (let i = 0; i < 100; i++) {
    data.push(['Value ' + (i + 1)]);
  }
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

This tip is applicable to almost all Google Workspace services. Collect your data, then perform a single bulk read or write operation.
Optimize Performance with Batch Operations
Google Workspace + Gemini partner offer

Upgrade to Google Workspace Business with your custom domain and built-in Gemini AI

Get a 14-day free trial and a discount on your first year of Google Workspace Business (professional Gmail, secure Drive, Meet, Docs, Sheets, and built-in Gemini AI).

Try Google Workspace for free Starter / Standard / Plus guide