Last active
December 20, 2023 07:23
-
-
Save darinalleman/a755f9485fd6b0c12342946edd2043a3 to your computer and use it in GitHub Desktop.
Zillow Tiller Sheets Importer (With Balance History)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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"); | |
} | |
} |
What values do you use for %AccountType% and %AccountClass% ?
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
@darinalleman What values do you use for %AccountType% and %AccountClass% ?