Scenario:
As a Google Workspace Admin, you want to automate the process of exporting audit logs to Google Sheets for real-time monitoring, data analysis, and compliance reporting—without manual intervention.
You can automatically send Google Workspace Reporting, Audit, and Investigation logs to Google Sheets using Google Apps Script and Google Admin SDK Reports API. Here’s a step-by-step guide
Step 1: Enable APIs
- Go to Google Cloud Console.
- Select your project. (If not Create New)
- Navigate to APIs & Services > Enabled APIs & Services.
- Enable the following APIs:
- Admin SDK API.
- Google Sheets API.
Step 2: Generate OAuth 2.0 Credentials
- Go to APIs & Services > Credentials.
- Click + CREATE CREDENTIALS > OAuth Client ID.
- Choose Web Application as the application type.
- Add the following in Authorized Redirect URIs
https://developers.google.com/oauthplayground
5. Click Create and download the Client ID JSON file
Step 3: Get Access and Refresh Tokens
- Open OAuth 2.0 Playground.
- Click the Settings (gear icon) in the top-right corner.
- Enable Use your own OAuth credentials.
- Enter your Client ID and Secret.
- Select the required scopes:
https://www.googleapis.com/auth/admin.reports.audit.readonly
https://www.googleapis.com/auth/spreadsheets
6. Click Authorize APIs and allow permissions.
7. Copy the Access Token and Refresh Token.
Step 4: Write the Script in Google Sheets
- Open the Google Sheet.
- Go to Extensions > Apps Script.
- Store Tokens in Script Properties
- Go to Project Settings > Script Properties > Add a Property.
- Add the following key-value pairs
- Save the properties
ACCESS_TOKEN = <Your Access Token>
REFRESH_TOKEN = <Your Refresh Token>
CLIENT_ID = <Your Client ID>
CLIENT_SECRET = <Your Client Secret>
Add the following code to your Apps Script
const CLIENT_ID = PropertiesService.getScriptProperties().getProperty('CLIENT_ID');
const CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('CLIENT_SECRET');
const REFRESH_TOKEN = PropertiesService.getScriptProperties().getProperty('REFRESH_TOKEN');
// Function to refresh access token
function refreshAccessToken() {
const tokenUrl = 'https://oauth2.googleapis.com/token';
const payload = {
client_id: CLIENT_ID,
client_secret: CLIENT_SECRET,
refresh_token: REFRESH_TOKEN,
grant_type: 'refresh_token'
};
const options = {
method: 'post',
contentType: 'application/x-www-form-urlencoded',
payload: payload
};
const response = UrlFetchApp.fetch(tokenUrl, options);
const result = JSON.parse(response.getContentText());
if (result.access_token) {
PropertiesService.getScriptProperties().setProperty('ACCESS_TOKEN', result.access_token);
Logger.log('New Access Token: ' + result.access_token);
} else {
Logger.log('Failed to refresh token: ' + result.error);
}
}
Update the access token with the dynamic property
function fetchAuditLogs() {
const ACCESS_TOKEN = PropertiesService.getScriptProperties().getProperty('ACCESS_TOKEN');
const SHEET_ID = 'YOUR_GOOGLE_SHEET_ID'; // Replace with your Sheet ID
const url = 'https://admin.googleapis.com/admin/reports/v1/activity/users/all/applications/admin';
const options = {
method: 'get',
headers: {
Authorization: 'Bearer ' + ACCESS_TOKEN
},
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText());
const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
// Add headers if sheet is empty
if (sheet.getLastRow() === 0) {
sheet.appendRow(['Actor Email', 'Event Name', 'Date', 'Description']);
}
data.items.forEach(item => {
const actor = item.actor.email || 'N/A';
const date = item.id.time || 'N/A';
const eventName = item.events[0].name || 'N/A';
// Extracting the event description
let description = '';
const params = item.events[0].parameters || [];
params.forEach(param => {
description += `${param.name}: ${param.value}\n`; // Combine all parameters
});
// Append row to sheet
sheet.appendRow([actor, eventName, date, description]);
});
Logger.log('Audit logs with descriptions added successfully!');
} catch (error) {
Logger.log('Error: ' + error.message);
// Refresh access token if 401 error occurs
if (error.message.includes('401')) {
refreshAccessToken();
}
}
}
Click Run to test your script and check google sheets data are added.
Step 5: Set Triggers for Automation
- Schedule Automated Updates (Optional):
- Go to Edit > Triggers.
- Create a trigger to run this script daily or hourly.
Final Outputs:
- Automatically creates headers (Actor Email, Date, Event Name, Description) if the sheet is empty.
- Combine (
nameandvalue) from each event and appends them in the Description column. - Automatically attempts to refresh tokens if a 401 error occurs.
- Useful for compliance and IT Audit reports.
My Recommendation
After pulling the whole Audit logs, you can alter the query to collect last 24 hours and set the trigger every day on your preferred time.
- Filter by Date
- Add query parameters to the URL, e.g., fetch logs from the past 24 hours
const url = 'https://admin.googleapis.com/admin/reports/v1/activity/users/all/applications/admin?startTime=' + new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString();