Zillow Tiller Sheets Importer (With Balance History)

Code.gs

function writeZestimateToSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var zillowSheet = sheets.find(sheet => sheet.getSheetName() == 'Zillow');
  var balanceHistory = sheets.find(sheet => sheet.getSheetName() == 'Balance History');
  if (zillowSheet && balanceHistory) {
    var zillowIds = ['%%%%%%%%%'];
    var row = [];
    for (i = 0; i < zillowIds.length; i++) {
      if (zillowIds[i] != undefined){
        var html = UrlFetchApp.fetch('https://www.zillow.com/homes/' + zillowIds[i] + '_zpid/').getContentText();
        const divRegex = /(<div class="zestimate-value">)(\$)(\d{1,3})(,\d{3})*(<\/div>)/g;
        const moneyRegex = /(\$)(\d{1,3})(,\d{3})*/g;
        var div = html.match(divRegex);
        var zestimate;
        if (div && div[0]) {
          zestimate = div[0].match(moneyRegex)[0];
          if (zestimate) {
            try {
              var today = new Date();
              var lastSunday = new Date();
              lastSunday.setDate(today.getDate() - today.getDay());
              let rowIndex = (i * 2);
              row[rowIndex] = zestimate;
              row[rowIndex + 1] = today;
              //add one record to the balance history sheet
              var balanceRow = [];
              balanceRow[0] = '';
              balanceRow[1] =  (today.getMonth()+1)  + "/" + today.getDate() + "/" + today.getFullYear().toString().slice(-2);//m/d/yy
              balanceRow[2] = today.getHours() + ':' + today.getMinutes() + (today.getHours() > 12 ? ' PM' : ' AM');//time
              balanceRow[3] = '%%%%ManualAccountName%%%%%';
              balanceRow[4] = '';
              balanceRow[5] = 'manual:%%ManualAccountId%%';
              balanceRow[6] = '';
              balanceRow[7] = zestimate;
              balanceRow[8] = (today.getMonth()+1) + "/1/" + today.getFullYear().toString().slice(-2);//month
              balanceRow[9] = (lastSunday.getMonth()+1)  + "/" + lastSunday.getDate() + "/" + lastSunday.getFullYear().toString().slice(-2);//m/d/yy
              balanceRow[10] = '%AccountType%';
              balanceRow[11] = '%AccountClass%';
              balanceHistory.insertRows(3, 1);//shift all rows down by one from row 3
              balanceHistory.getRange(3,1,1,12).setValues([balanceRow]);
            } catch(error) {
              throw new Error('Setting values failed');
            }
          }
          else {
            throw new Error("Zestimate not found");
          }
        }
        else {
          throw new Error("Zestimate div not found on page");
        }
      }
      Utilities.sleep(10000);//wait 10 seconds before doing the next request
    }
    zillowSheet.appendRow(row);
    MailApp.sendEmail('%[email protected]%', 'Zestimate Update', 'As of '.concat(row[1].toString().concat(' the Zestimate of %MY ADDRESS% is ').concat(row[0].toString())));

  }
  else {
    throw new Error("Could not find Zillow sheet");
  }
}

@darinalleman What values do you use for %AccountType% and %AccountClass% ?

What values do you use for %AccountType% and %AccountClass% ?

@ejaramilla,

AccountType refers to the 'Type' column in Balance History which references the 'Class Override' column in the Accounts sheet. For me, it's either 'Asset' or 'Liability'.

AccountClass is the 'Class' column in Balance History which references 'Group' in the accounts sheet. For me, it ends up being cash, credit, mortgage, property, etc.

Darin where would i paste this to exactly sir? Appreciate the time and help.

@mulamba12 unfortunately this script no longer works due to Zillow’s pretty strict anti-bot and anti-scraping. They pretty much detect that it’s a script accessing the page and prompt with the “I’m not a robot” popup which this script doesn’t handle.

@darinalleman Thank you for taking the time to respond, its greatly appreciated. Pray all is well with you and yours!