Skip to content

Instantly share code, notes, and snippets.

@darinalleman
Last active December 20, 2023 07:23
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save darinalleman/a755f9485fd6b0c12342946edd2043a3 to your computer and use it in GitHub Desktop.
Save darinalleman/a755f9485fd6b0c12342946edd2043a3 to your computer and use it in GitHub Desktop.
Zillow Tiller Sheets Importer (With Balance History)
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('%ME@EMAIL.COM%', '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");
}
}
@ejaramilla
Copy link

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

@darinalleman
Copy link
Author

darinalleman commented Jan 26, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment