Script for Google Spreadsheet: Parse GMail messages for SketchFab sales and feed the spreadsheet with the data.

3DSales.js

function CalcMonthly() {
  let database = SpreadsheetApp.getActive();
  let sfTable = database.getSheetByName("SketchFabTable");
  var sfMonthly = database.getSheetByName("Dashboard");
  sfMonthly.clear();
  sfMonthly.appendRow(["Month", "Earnings", "Number of Sales"]);
  var count = sfTable.getLastRow();
  
  var currMonth = -1;
  var currYear = -1;
  var sum = 0.0;
  var sales = 0;

  for (i = 2; i <= count; i++) {
    var r = sfTable.getRange(i, 1, 1, 3);
    values = r.getValues();
    var d = new Date(values[0][2]);
    var earnings = values[0][1];
    
    var m = d.getMonth();
    var y = d.getFullYear();

    if (m != currMonth) {
      if (currMonth != -1) {
        var monthString = (currMonth + 1).toString() + "/" + currYear;
        sfMonthly.appendRow([monthString, sum, sales]);
        Logger.log(sum);
      }
      currMonth = m;
      currYear = y;       
      sum = earnings;
      sales = 1;
    } else {
      sum = sum + earnings;
      sales++;
    }

    if (i == count) {
        var monthString = (currMonth + 1).toString() + "/" + d.getFullYear();
        sfMonthly.appendRow([monthString, sum, sales]);
        Logger.log(sum);
    }
  }
}

function Update_sketchfab() {
   Logger.log("Searching emails...");
  let sketchfabQ = 'from:(notifications@sketchfab.com) subject:"You earned"';
  let searchPos = 0;
  let database = SpreadsheetApp.getActive();
  let sheet = database.getSheetByName("SketchFabTable");
  let firstTimeEver = false;
  let lastCheckedOutID = undefined;
  let insertRowPos = 0

  if (sheet.getLastRow()==0 ) {
    //first time/
    sheet.appendRow(["name", "earnings", "date", "email_id"]);
    firstTimeEver = true;
  } else {
    let r = sheet.getRange(2,1,1,4)
    lastCheckedOutID = r.getValues()[0][3];
    insertRowPos = 2;
  }

  while (true) 
  {
    let threads = GmailApp.search(sketchfabQ, searchPos,20);
    searchPos += 20;

    if (threads.length == 0) return;    

    Logger.log(threads.length);
    
    for (let i = 0; i < threads.length; i++)
    {
      let count = threads[i].getMessageCount();
      let subject = threads[i].getFirstMessageSubject();    
      
      let msgs = threads[i].getMessages()
      for (let j=0; j<count; j++)
      {
        let s = msgs[j].getPlainBody()
        let products = ParseSketchFabMsg(s);
        for (let i=0; i<products.length; i++)
        {
          const ps = products[i];
          ps.date = msgs[j].getDate();
          ps.id = msgs[j].getId();
          Logger.log("%s\t%s\t%s\t%s\t%s",ps.name, ps.earnings, ps.price, ps.date, ps.id);

          // If not firstTimeEver then insert row, do not append.
          // if not firstTimeEver and lastCheckedOutID == ps.id then exit function
          if (firstTimeEver) {
            sheet.appendRow([ps.name, ps.earnings, ps.date, ps.id]);
          } else {
            if (lastCheckedOutID != ps.id) {            
              sheet.insertRowBefore(insertRowPos);
              r = sheet.getRange(insertRowPos,1,1,4);
              r.setValues([[ps.name, ps.earnings, ps.date, ps.id]])
              insertRowPos++;
            } else {
              return;
            }
          }
          
        }
    
      }
    }
  }

}

function UpdateAll()
{
  Update_sketchfab();
  CalcMonthly();
}

function onOpen(e) {
    SpreadsheetApp.getUi()
      .createMenu('3D Sales')
      .addItem('Update Sketchfab', 'UpdateAll')      
      .addToUi();
}


function ParseSketchFabMsg(text)
{
  let lines = text.split("\n");
  let idx = lines.indexOf("Item ordered");
  let linePos = idx+1;
  let total = 0;
  let gross = 0;
  const products = [];  
  while (linePos < lines.length && total == 0)
  {    
    let s = lines[linePos];
    if (s.indexOf("[image:")==0) 
    {      
      const prod = {name:s.substring(8,s.length-2), price:parseFloat(lines[linePos+5].substr(1))}      
      gross = gross + prod.price;
      products.push(prod)
    }

    if (s.indexOf("*Total:")==0) {
      total = parseFloat(s.substring(9, s.length-2));
    }
    linePos++;
  }  
  //fix earnings by product
  const factor = total/gross;
  for (i=0; i<products.length; i++)
  {
    products[i].earnings = products[i].price * factor;
  }
  return products;
}