Convert Google Sheet script to work in Airtable

Chasity_Nycole · ‎Oct 27, 2020

I use the below script to get a state depending on the zip code from a cell. How can I format it to work in Airtable?

function getState(input) {


/* Ensure param is a string to prevent unpredictable parsing results */
if (typeof input !== ‘string’) {
console.log(‘Must pass the zipcode as a string.’);
return;
}

/* Ensure we have exactly 5 characters to parse */
if (input.length !== 5) {
console.log(‘Must pass a 5-digit zipcode.’);
return;
}

/* Ensure we don’t parse strings starting with 0 as octal values */
const zipcode = parseInt(input, 10);

let st;
let state;

/* Code cases alphabetized by state */
if (zipcode >= 35000 && zipcode <= 36999) {
st = ‘AL’;
state = ‘Alabama’;
} else if (zipcode >= 99500 && zipcode <= 99999) {
st = ‘AK’;
state = ‘Alaska’;
} else if (zipcode >= 85000 && zipcode <= 86999) {
st = ‘AZ’;
state = ‘Arizona’;
} else if (zipcode >= 71600 && zipcode <= 72999) {
st = ‘AR’;
state = ‘Arkansas’;
} else if (zipcode >= 90000 && zipcode <= 96699) {
st = ‘CA’;
state = ‘California’;
} else if (zipcode >= 80000 && zipcode <= 81999) {
st = ‘CO’;
state = ‘Colorado’;
} else if (zipcode >= 6000 && zipcode <= 6999) {
st = ‘CT’;
state = ‘Connecticut’;
} else if (zipcode >= 19700 && zipcode <= 19999) {
st = ‘DE’;
state = ‘Delaware’;
} else if (zipcode >= 32000 && zipcode <= 34999) {
st = ‘FL’;
state = ‘Florida’;
} else if (zipcode >= 30000 && zipcode <= 31999) {
st = ‘GA’;
state = ‘Georgia’;
} else if (zipcode >= 96700 && zipcode <= 96999) {
st = ‘HI’;
state = ‘Hawaii’;
} else if (zipcode >= 83200 && zipcode <= 83999) {
st = ‘ID’;
state = ‘Idaho’;
} else if (zipcode >= 60000 && zipcode <= 62999) {
st = ‘IL’;
state = ‘Illinois’;
} else if (zipcode >= 46000 && zipcode <= 47999) {
st = ‘IN’;
state = ‘Indiana’;
} else if (zipcode >= 50000 && zipcode <= 52999) {
st = ‘IA’;
state = ‘Iowa’;
} else if (zipcode >= 66000 && zipcode <= 67999) {
st = ‘KS’;
state = ‘Kansas’;
} else if (zipcode >= 40000 && zipcode <= 42999) {
st = ‘KY’;
state = ‘Kentucky’;
} else if (zipcode >= 70000 && zipcode <= 71599) {
st = ‘LA’;
state = ‘Louisiana’;
} else if (zipcode >= 3900 && zipcode <= 4999) {
st = ‘ME’;
state = ‘Maine’;
} else if (zipcode >= 20600 && zipcode <= 21999) {
st = ‘MD’;
state = ‘Maryland’;
} else if (zipcode >= 1000 && zipcode <= 2799) {
st = ‘MA’;
state = ‘Massachusetts’;
} else if (zipcode >= 48000 && zipcode <= 49999) {
st = ‘MI’;
state = ‘Michigan’;
} else if (zipcode >= 55000 && zipcode <= 56999) {
st = ‘MN’;
state = ‘Minnesota’;
} else if (zipcode >= 38600 && zipcode <= 39999) {
st = ‘MS’;
state = ‘Mississippi’;
} else if (zipcode >= 63000 && zipcode <= 65999) {
st = ‘MO’;
state = ‘Missouri’;
} else if (zipcode >= 59000 && zipcode <= 59999) {
st = ‘MT’;
state = ‘Montana’;
} else if (zipcode >= 27000 && zipcode <= 28999) {
st = ‘NC’;
state = ‘North Carolina’;
} else if (zipcode >= 58000 && zipcode <= 58999) {
st = ‘ND’;
state = ‘North Dakota’;
} else if (zipcode >= 68000 && zipcode <= 69999) {
st = ‘NE’;
state = ‘Nebraska’;
} else if (zipcode >= 88900 && zipcode <= 89999) {
st = ‘NV’;
state = ‘Nevada’;
} else if (zipcode >= 3000 && zipcode <= 3899) {
st = ‘NH’;
state = ‘New Hampshire’;
} else if (zipcode >= 7000 && zipcode <= 8999) {
st = ‘NJ’;
state = ‘New Jersey’;
} else if (zipcode >= 87000 && zipcode <= 88499) {
st = ‘NM’;
state = ‘New Mexico’;
} else if (zipcode >= 10000 && zipcode <= 14999) {
st = ‘NY’;
state = ‘New York’;
} else if (zipcode >= 43000 && zipcode <= 45999) {
st = ‘OH’;
state = ‘Ohio’;
} else if (zipcode >= 73000 && zipcode <= 74999) {
st = ‘OK’;
state = ‘Oklahoma’;
} else if (zipcode >= 97000 && zipcode <= 97999) {
st = ‘OR’;
state = ‘Oregon’;
} else if (zipcode >= 15000 && zipcode <= 19699) {
st = ‘PA’;
state = ‘Pennsylvania’;
} else if (zipcode >= 300 && zipcode <= 999) {
st = ‘PR’;
state = ‘Puerto Rico’;
} else if (zipcode >= 2800 && zipcode <= 2999) {
st = ‘RI’;
state = ‘Rhode Island’;
} else if (zipcode >= 29000 && zipcode <= 29999) {
st = ‘SC’;
state = ‘South Carolina’;
} else if (zipcode >= 57000 && zipcode <= 57999) {
st = ‘SD’;
state = ‘South Dakota’;
} else if (zipcode >= 37000 && zipcode <= 38599) {
st = ‘TN’;
state = ‘Tennessee’;
} else if ( (zipcode >= 75000 && zipcode <= 79999) || (zipcode >= 88500 && zipcode <= 88599) ) {
st = ‘TX’;
state = ‘Texas’;
} else if (zipcode >= 84000 && zipcode <= 84999) {
st = ‘UT’;
state = ‘Utah’;
} else if (zipcode >= 5000 && zipcode <= 5999) {
st = ‘VT’;
state = ‘Vermont’;
} else if (zipcode >= 22000 && zipcode <= 24699) {
st = ‘VA’;
state = ‘Virgina’;
} else if (zipcode >= 20000 && zipcode <= 20599) {
st = ‘DC’;
state = ‘Washington DC’;
} else if (zipcode >= 98000 && zipcode <= 99499) {
st = ‘WA’;
state = ‘Washington’;
} else if (zipcode >= 24700 && zipcode <= 26999) {
st = ‘WV’;
state = ‘West Virginia’;
} else if (zipcode >= 53000 && zipcode <= 54999) {
st = ‘WI’;
state = ‘Wisconsin’;
} else if (zipcode >= 82000 && zipcode <= 83199) {
st = ‘WY’;
state = ‘Wyoming’;
} else {
st = ‘none’;
state = ‘none’;
console.log(‘No state found matching’, zipcode);
}

return state;
}

Bill_French · ‎Oct 27, 2020

You want to use this as a formula in Airtable, or in a Script in Airtable?

Chasity_Nycole · ‎Oct 27, 2020

A formula would be best since I’d like it to return a state automatically.

Chasity_Nycole · ‎Oct 27, 2020

Hi, I typed in this formula but it is saying there is an error. I’m not sure where it would be because I copied and pasted each IF statement with the closing parentheses.
…
IF(AND({Zip Code}>=75000,{Zip Code}<=79999), “Texas”, IF(AND({Zip Code}>=20000,{Zip Code}<=20599), “Washington, DC”, IF(AND({Zip Code}>=20600,{Zip Code}<=21999), “Maryland”, IF(AND({Zip Code}>=35000,{Zip Code}<=36999), “Alabama”, IF(AND({Zip Code}>=99500,{Zip Code}<=99999), “Alaska”, IF(AND({Zip Code}>=85000,{Zip Code}<=86999), “Arizona”, IF(AND({Zip Code}>=71600,{Zip Code}<=72999), “Arkansas”, IF(AND({Zip Code}>=90000,{Zip Code}<=96699), “California”, IF(AND({Zip Code}>=80000,{Zip Code}<=81999), “Colorado”, IF(AND({Zip Code}>=6000,{Zip Code}<=6999), “Connecticut”, IF(AND({Zip Code}>=19700,{Zip Code}<=19999), “Delaware”, IF(AND({Zip Code}>=32000,{Zip Code}<=34999), “Florida”, IF(AND({Zip Code}>=30000,{Zip Code}<=31999), “Georgia”, IF(AND({Zip Code}>=96700,{Zip Code}<=96999), “Hawaii”, IF(AND({Zip Code}>=83200,{Zip Code}<=83999), “Idaho”, IF(AND({Zip Code}>=60000,{Zip Code}<=62999), “Illinois”, IF(AND({Zip Code}>=46000,{Zip Code}<=47999), “Indiana”, IF(AND({Zip Code}>=50000,{Zip Code}<=52999), “Iowa”, IF(AND({Zip Code}>=66000,{Zip Code}<=67999), “Kansas”, IF(AND({Zip Code}>=40000,{Zip Code}<=42999), “Kentucky”, IF(AND({Zip Code}>=70000,{Zip Code}<=71599), “Louisiana”, IF(AND({Zip Code}>=3900,{Zip Code}<=4999), “Maine”, IF(AND({Zip Code}>=1000,{Zip Code}<=2799), “Massachusetts”, IF(AND({Zip Code}>=48000,{Zip Code}<=49999), “Michigan”, IF(AND({Zip Code}>=55000,{Zip Code}<=56999), “Minnesota”, IF(AND({Zip Code}>=38600,{Zip Code}<=39999), “Mississippi”, IF(AND({Zip Code}>=63000,{Zip Code}<=65999), “Missouri”, IF(AND({Zip Code}>=59000,{Zip Code}<=59999), “Montana”, IF(AND({Zip Code}>=27000,{Zip Code}<=28999), “North Carolina”, IF(AND({Zip Code}>=58000,{Zip Code}<=58999), “North Dakota”, IF(AND({Zip Code}>=68000,{Zip Code}<=69999), “Nebraska”, IF(AND({Zip Code}>=88900,{Zip Code}<=89999), “Nevada”, IF(AND({Zip Code}>=3000,{Zip Code}<=3899), “New Hampshire”, IF(AND({Zip Code}>=7000,{Zip Code}<=8999), “New Jersey”, IF(AND({Zip Code}>=87000,{Zip Code}<=88499), “New Mexico”, IF(AND({Zip Code}>=10000,{Zip Code}<=14999), “New York”, IF(AND({Zip Code}>=43000,{Zip Code}<=45999), “Ohio”, IF(AND({Zip Code}>=73000,{Zip Code}<=74999), “Oklahoma”, IF(AND({Zip Code}>=97000,{Zip Code}<=97999), “Oregon”, IF(AND({Zip Code}>=15000,{Zip Code}<=19699), “Pennsylvania”, IF(AND({Zip Code}>=300,{Zip Code}<=999), “Puerto Rico”, IF(AND({Zip Code}>=2800,{Zip Code}<=2999), “Rhode Island”, IF(AND({Zip Code}>=29000,{Zip Code}<=29999), “South Carolina”, IF(AND({Zip Code}>=57000,{Zip Code}<=57999), “South Dakota”, IF(AND({Zip Code}>=37000,{Zip Code}<=38599), “Tennessee”, IF(AND({Zip Code}>=88500,{Zip Code}<=88599), “Texas”, IF(AND({Zip Code}>=84000,{Zip Code}<=84999), “Utah”, IF(AND({Zip Code}>=5000,{Zip Code}<=5999), “Vermont”, IF(AND({Zip Code}>=22000,{Zip Code}<=24699), “Virginia”, IF(AND({Zip Code}>=98000,{Zip Code}<=99499), “Washington”, IF(AND({Zip Code}>=53000,{Zip Code}<=54999), “Wisconsin”, IF(AND({Zip Code}>=82000,{Zip Code}<=83199), “Wyoming”, “Other”)))))))))))))))))))))))))))))))))))))))))))))))))))))
…

Bill_French · ‎Oct 27, 2020

Start with just three states in your new formula - validate it works; then add the new states. I recommend building the formulas in a text editor so you can easily create and sustain a pattern. Then copy new versions into the formula field and test with each incremental edit.

Chasity_Nycole · ‎Oct 27, 2020

I did that, but now I see that it only works for up to 10 of the IF statements. I now am searching if there is a limit to the number of nested functions.

Bill_French · ‎Oct 27, 2020

I’ve seen some very long formulas before, so I doubt you’re at the limit. More likely you’re seeing a logic issue.

Another suggestion - consider Switch() instead of IF() if possible. It may help to simplify the code perhaps.