***

melodiccrypter.js

// Developer: Hugh Caluscusin
// Git: @MelodicCrypter

// Mutable states
let colsData = [1,2,3,4]; // example -> A,B,C,D
let colResult = 5; // example -> E

const ERR_BG_COLOR="#EEB8AE",SEMI_CORRECT_BG_COLOR="#CFE2F3",CORRECT_BG_COLOR="#86FFBA";function onOpen(e){SpreadsheetApp.getUi().createAddonMenu().addItem("Run","runAddressLookup").addSeparator().addItem("Revert","revertResultsColumn").addToUi()}function onInstall(e){onOpen(e)}function setting(e,t){e&&(colsData=e),t&&(colResult=t)}function runAddressLookup(){Logger.log("\uD83D\uDD25 Script is running.");let e=SpreadsheetApp.getActiveSheet(),t=e.getDataRange().getValues(),o=Maps.newGeocoder();o.setLanguage("it");let n=colResult,a=colsData.map(e=>Number(e)-1);if(0===t.length)throw Error("The file is empty!");if(t.length>0&&t.length<2)throw Error("Row 1 is expected for Headers only! Put real data starting on row 2.");t&&t.forEach((t,o)=>{if(0===o||(console.log("address",""===t[a[0]]),console.log("city",""===t[a[1]]),console.log("postal",""===t[a[2]]),console.log("prov",""===t[a[3]]),""===t[a[0]]||""===t[a[1]]||""===t[a[2]]||""===t[a[3]]))return;let r=t[a[0]],s=t[a[1]],g=Number(t[a[2]]).toFixed(0),l=t[a[3]],d=`${r} ${s} ${g} ${l}`;console.log("addressToSearch",d);let i=UrlFetchApp.fetch("https://addresslookup-xwobm.ondigitalocean.app/verify",{method:"post",muteHttpExceptions:!0,payload:{address:d}}),u=i.toString(),R=e.getRange(o+1,n);if(Logger.log(d),"VALID"===u){R.setValue(d),R.setBackground("#86FFBA");return}R.setValue("INVALID_ADDRESS_OR_ERROR"),R.setBackground("#EEB8AE")})}function revertResultsColumn(){let e=SpreadsheetApp.getActiveSheet(),t=e.getLastRow(),o=e.getRange(2,colResult,t-1,1);o.clear()}function testSetting(){console.log("testSetting: ",colsData,colResult)}