Stacked IFs Google Sheets Apps Script

Question

New to Google Apps Script. Could someone tell me to to write stacked IFs in Apps Script. I want to create a custom function that calculates the total sixteenths in a custom feet-inches-sixteens format. The format is different depending on the length of the string. 1=1', 12=12',508 = 5 1/2", 1604 = 16 1/4", etc...and in order to convert to feet-inches-sixteen I have to calculate the total 16ths first. Any help is appreciated.

What research did you do till now? Do you have a code you're working on? Can you provide a copy of the spreadsheet you are working on, free of sensitive information, clearly indicating the desired outcome? — Iamblichus
Sorry for not being more clear in my question, but the format itself is a bit confusing. Here's a link to a spreadsheet that might explain things better. docs.google.com/spreadsheets/d/… — Geo

mary mary · Accepted Answer · 2021-03-13T22:52:03

Your question is a little hard to understand, but I think you have values in sheets represented as 1' 3 1/2" (1 ft, 3.5 in) which you'd like to convert to sixteenth-inch units.

The following function should do what you want:

function SIXTEENTHS(string) {
  var regex = /^\s*([0-9]*(?=\'))?\'?\s*([0-9]+(?![0-9]))?\s*([0-9]+\/[0-9]+)?\"?\s*$/;
  var parsed = string.match(regex);
  if (!parsed) {
    return 'Bad input';
  }
  var inches = (parsed[1] | 0) * 12; // Parse feet
  inches += (parsed[2] | 0); // Parse inches
  if (parsed[3]) {
    inches += (parsed[3].split('/').reduce((a, b) => a/b)); // Parse fractions
  }
  return inches*16;
}

Parsing the string is a little tricky, but you can see what the regex is doing and test it at Regex 101.

Stacked IFs Google Sheets Apps Script

1 Answers