Email Spreadsheet Revision Changes

diffChecker.gs

function getFolderId() { return '[Your Drive Folder Id]' /*Keys/Directory Review*/ }

function flatten(arr) { return [].concat.apply([], arr) }

function diffs(str1, str2, color) {
  let diffs = [], arr1 = str1.replace(/\n/, '').split('\t').join(' ').split(' ')
  const arr2 = str2.replace(/\n/, '').split('\t').join(' ').split(' ')
  arr1.forEach((val, i) => val != arr2[i] && diffs.push(val))
  diffs.forEach(val => arr1[arr1.indexOf(val)] = `<span style="background:${color}">${val}</span>`)
  return arr1.join(' ')+'\n'
}

function importJS(url) {
  const js = UrlFetchApp.fetch(url).getContentText()
  return eval(js)
}

function logToTextFile(filename, content) {
  const files = DriveApp.getFilesByName(filename)
  const folderId = getFolderId()
  if (files.hasNext()) { return files.next().setContent(content) }
  else { return DriveApp.getFolderById(folderId).createFile(filename, content) }
}

function sheetData(sheetId) {
  return {
    spreadsheet: SpreadsheetApp.openById('[Your Sheet Id]'), 
    sheetId,
    startRow: 1,
    startCol: { letter: 'A', num: 1 },
    endCol: { letter: 'D', num: 4 }
  }
}

function getSheetById(spreadsheet, sheetId) {
  return spreadsheet.getSheets().filter(sheet => sheet.getSheetId() === sheetId)[0]
}

function diffChecker(filename, gid) {
  const dmpLib = importJS('https://cdnjs.cloudflare.com/ajax/libs/diff_match_patch/20121119/diff_match_patch.js')
  const { sheetId, spreadsheetId, spreadsheet, startRow } = sheetData(gid)
  const startColLetter = sheetData().startCol.letter
  const endColLetter = sheetData().endCol.letter
  const sheet = getSheetById(spreadsheet, sheetId)
  const endRow = sheet.getRange('!A1:A').getValues().filter(String).length+1
  const rangeString = startColLetter+startRow+':'+endColLetter+endRow
  const range = sheet.getRange(rangeString)
  const data = range.getValues()
  const sheetTxt = data.map(row => flatten(row)).map(row => row.join('\t')).join('\n')
  const folderId = getFolderId()
  const files = DriveApp.getFolderById(folderId).getFilesByName(filename)
  
  let previousRevisionText, newRevisionText = ''
  
  // if filename exists
  if (files.hasNext()) {
    previousRevisionText = files.next().getBlob().getDataAsString() // store previous revision
    newRevisionText = sheetTxt // store new revision
    logToTextFile(filename, sheetTxt) // write new revision
  } else {
    return logToTextFile(filename, sheetTxt) // create initial file and exit
  }

  // diff check
  function diff_lineMode(text1, text2) {
    const dmp = new diff_match_patch()
    const a = dmp.diff_linesToChars_(text1, text2)
    const lineText1 = a.chars1
    const lineText2 = a.chars2
    const lineArray = a.lineArray
    const diffs = dmp.diff_main(lineText1, lineText2, false)
    dmp.diff_charsToLines_(diffs, lineArray)
    return diffs
  }
  
  const result = diff_lineMode(previousRevisionText, newRevisionText)
  
  // format results
  function format(sisterType, sisterStr, type, str, color = '#fff') {
    const red = '#fdb8c0'
    const green = '#acf2bd'
    let formatStr = str
    
    if (type == '-' && sisterType == 1) {
      formatStr = diffs(str, sisterStr, red)
    }
    if (type == '+' && sisterType == -1) {
      formatStr = diffs(str, sisterStr, green)
    }
    
    return `<br><span style='color: ${color}'><strong>${type}</strong> ${formatStr.split('\t').join(' ')}</span>`.split('\n').join('<br>')
  }
  const htmlLog = result.map((line, i) => line[0] == -1 ? format(result[i+1][0], result[i+1][1], '-', line[1], 'crimson') : line[0] == 1 ? format(result[i-1][0], result[i-1][1], '+', line[1], 'green'): '').join('')
  
  // email results
  return MailApp.sendEmail({
    to: '[Your Email]',
    subject: 'Diff Checker: '+filename,
    htmlBody: `🦊Auto Emailer<br><br><pre><code>${htmlLog}</code></pre>`
  })
  
}

// trigger weekly
function eps() { return diffChecker('eps.txt', [Your Sheet GID]) }
function ensc() { return diffChecker('ensc.txt', [Your Sheet GID]) }