Google Apps Script for fetching Google Analytics Insight Alerts and logging them in a Google Sheet

Code.gs

/** Google Analytics Insights fetcher
* 
* This script automates the collection of Google Analytics “new insight” emails and turns them into a structured log 
* in a shared spreadsheet. From there, you can build a dashboard in Looker Studio or another tool to monitor alerts for 
* one or more GA properties over time.
* To use the script, set the configuration variables below, and add a trigger to run the script hourly or daily. 
* When it runs, it searches the Gmail inbox of the account the script is running under and looks
* for any “new insight” notifications received since the previous day, and logs the the property name, metric, frequency, 
* alert text, and a link to the alert in GA. It filters out insights you’ve already logged, and writes only the new records 
* into an “insights” sheet. 
* There’s also a backfill option to write older alerts to the Sheet.
*/

// The THREADSTORETRIEVE variable tells the script how many email threads to retrieve from the inbox
// it filters for insight emails, so 100 should be plenty for most circumstances, but increase as needed
const THREADS_TO_RETRIEVE = 100;
// Make sure to change this to match the Sheet ID of your Sheet
const SHEET_ID = '11Ww8ZS_dwSKsjWpw056hQKdSTmi_hE1Eh0p6MUAEXu8';
// This is what the script looks for in message subject lines - needs to be changed if your GA interface is non-English
const SUBJECT_CONTAINS = 'new insight';
// Determines how far back the script checks when running backfillInsights()
// Consider also increasing THREADS_TO_RETRIEVE when backfilling if you get a lot of alerts
const DAYS_TO_BACKFILL = 90;


// Trigger wrapper for scheduled runs.
function triggeredCheckForInsights() {
  checkForInsights(false);
}

// Manual backfill - run this once to get past alers, but 
function backfillInsights() {
  checkForInsights(true);
}

/**
 * Where it all happens. Fetches threads, parses insights, filters, and writes to sheet.
 */
function checkForInsights(backfill = false) {
  try {
    const sheet = getSheet();
    const today = new Date();
    const startDate = new Date(today);
    // defaults to backfilling 90 days, increase if you want to backfill more
    // you may also want to increase the THREADS_TO_RETRIEVE variable
    const dateOffset = (backfill) ? DAYS_TO_BACKFILL : 1; 
    startDate.setDate(startDate.getDate() - dateOffset);
    const dateStr = formatDateForSearch(startDate);
    const query = `subject:"${SUBJECT_CONTAINS}" after:${dateStr}`;
    const threads = GmailApp.search(query, 0, THREADS_TO_RETRIEVE);

    // parse all messages for insights
    let rows = [];
    threads.forEach(thread => {
      thread.getMessages().forEach(msg => {
        rows = rows.concat(getInsights(msg));
      });
    });

    // dedupe against existing sheet entries
    const existing = loadExisting(sheet, ['Property','Alert','Datetime']);
    rows = rows.filter(r => !isDuplicate(r, existing));

    // append to sheet
    appendRows(sheet, rows);

  } catch (err) {
    Logger.log(err);
    MailApp.sendEmail(
      Session.getEffectiveUser().getEmail(),
      'Insights Script Error',
      err.stack
    );
  }
}

/**
 * Extract insight rows from a single Gmail message.
 * Returns an array of [id, Date, property, metric, frequency, alert, link, "No", ""]
 */
function getInsights(message) {
  const body     = message.getPlainBody();
  const html     = message.getBody();
  const msgDate  = message.getDate();

  // Regex patterns to parse values from email
  const PROPERTY_RE       = /Property:\s*(.*?)\s*(?:On|From)/i; // parses the name of the GA property
  const MESSAGE_RE        = /([^>]+?)(?:<[^>]+>)*([^>]+?)(?:<[^>]+>)*View Details/g; // gets the content of the message
  const LINK_RE           = /\b(https?:\/\/\S+google\S+)\b/; // parses the link to the insight
  const INSIGHT_TEXT_RE   = />([^>]+)<\/p/; // parses the description of the insight

  let m = PROPERTY_RE.exec(body);
  let property = m && m[1] ? m[1].trim() : null;

  const insights = [];
  let match;
  while ((match = MESSAGE_RE.exec(html)) !== null) {
    // match[1] and match[2] capture parts around "View Details"
    const section = match[0];
    const linkM   = LINK_RE.exec(section);
    const textM   = INSIGHT_TEXT_RE.exec(section);
    const raw     = match[1].trim();
    const parts   = raw.split(' | ');

    const rowID    = generateUniqueId();
    const link     = linkM ? linkM[1] : 'unknown';
    const alertTxt = textM ? textM[1].trim() : 'unknown';

    if (parts.length === 4) {
      // [ property, metric, frequency, alertName ]
      insights.push([rowID,msgDate,property || parts[0],
        parts[1],parts[2],alertTxt,link]);
      property = property || parts[0];
    } else {
      insights.push([rowID,msgDate,property || 'unknown',
        'unknown','unknown',alertTxt,link]);
    }
  }

  return insights;
}

// ==== Helper Functions ====

/**
 * Open (or create) the given sheet in your spreadsheet.
 */
function getSheet() {
  const name = 'insights';
  const ss = SpreadsheetApp.openById(SHEET_ID);
  let sheet = ss.getSheetByName(name);
  const headers = ['Row ID', 'Datetime', 'Property', 'Metric', 'Frequency', 'Alert', 'Link'];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // in case the header row has been deleted
  if (!sheet) {
    sheet = ss.insertSheet(name);
  }
  return sheet;
}

/**
 * Format a Date as yyyy/MM/dd for Gmail searches.
 */
function formatDateForSearch(date) {
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
}

/**
 * Append rows to a sheet.  Optionally include a header row.
 */
function appendRows(sheet, rows) {
  if (!rows.length) return;
  const lastRow = sheet.getLastRow();
  const startRow = lastRow > 0 ? lastRow + 1 : 1;
  sheet.getRange(startRow, 1, rows.length, rows[0].length)
       .setValues(rows);
}

/**
 * Load existing rows into a nested object for quick duplicate checks.
 * Keys: existing[property][alert][datetimeString] = true
 */
function loadExisting(sheet, properties) {
  const data = sheet.getDataRange().getValues();
  const headers = data.shift();
  const idxs = properties.map(p => headers.indexOf(p));
  const dtIdx = headers.indexOf('Datetime');
  const alertIdx = headers.indexOf('Alert');
  const propIdx = headers.indexOf('Property');
  const existing = {};
  data.forEach(row => {
    const prop = row[propIdx];
    const alert= row[alertIdx];
    const dt   = row[dtIdx].toString();
    existing[prop] = existing[prop] || {};
    existing[prop][alert] = existing[prop][alert] || {};
    existing[prop][alert][dt] = true;
  });
  return existing;
}

/**
 * Check if a parsed row already exists.
 */
function isDuplicate(row, existing) {
  const dateKey = row[1].toString();
  const prop    = row[2];
  const alert   = row[5];
  return existing[prop] && existing[prop][alert] && existing[prop][alert][dateKey];
}

/**
 * Generate a simple unique ID.
 */
function generateUniqueId() {
  return Date.now() + '-' + Math.floor(Math.random() * 1000);
}