Sample Custom Function using URL Fetch (UrlFetchApp): Convert()

Sample Custom Function using URL Fetch (UrlFetchApp): Convert()

Report abuse

JRsays Level 2 6/19/09

I just did a quick blog post to show a custom function which uses UrlFetchApp to convert from one unit of measure to another - using Google Search in the background. 
The spreadsheet is here:
http://spreadsheets.google.com/ccc?key=r7PRNG-DcO3Qk33l8cVgYCQ

Here's the code for that script:

// CONVERT: Converts from one unit of measure to another
// Uses the Google Search "in" operator which allows conversion
//   of practically any units of measure which are sensible
//   including weights, distance, speed, volume, temperature,
//   currency (athough don't use these to trade), and probably more.
// Author:  Jonathan Rochelle (JR) - Google - reach me at http://jrsays.com

function convert(fromQty, fromUnits, toUnits) {
  if (!fromQty || !fromUnits || !toUnits) {
    return 'input?';
  }
  var q = 'http://www.google.com/search?q='+ fromQty + '+' + fromUnits + '+in+' + toUnits;
  var s = UrlFetchApp.fetch(q).getContentText();
// FIND the 3rd TABLE element in the results HTML
  var tableSpot = s.indexOf('<table');
  tableSpot = s.indexOf('<table',tableSpot+5);
  tableSpot = s.indexOf('<table',tableSpot+5);
// We are positioned in the THIRD TABLE in the results HTML
// Now - find the equal sign which has padding (the answer text)
  var equalSign = s.indexOf(' = ',tableSpot);
  var firstSpace = s.indexOf(' ', equalSign+3);
// do all the substringing to position us at the actual answer
  var charAfterSpace = trim(s.substr(firstSpace+1,1));
//  charAfterSpace = trim(charAfterSpace);
  var endOfNum = s.indexOf(' ',firstSpace+1);
  toQty = s.substr(equalSign+2, firstSpace - equalSign - 1);
  var answer = '';
  if (toQty.indexOf('doctype',0) > 0) { // the !doctype string is typical when there's an error
      answer = 'n/a';
    } else {
      // google result includes that weird � char instead of space
      // which is what we see in the web results - so i replace them.
      answer = parseFloat(toQty.replace(/�/g,''));
  }
  return answer;
}

function trim(stringToTrim) {
return stringToTrim.replace(/^\s+|\s+$/g,"");
}

Replies 1 - 3 of 3

NoodleGei Level 5 6/19/09

Hi JR,
very good example!
I'm waiting for more examples that replace the Import-Funktions (Break the limit of 50x Function-Calls ;-) per Sheet)
The new Class LanguageAPP work very fine for me ;-).

Is it possible, to make a Forum-Topic for complete Showcases / Code-Examples, to share all the Samples on one place?
And Example-Sheets for Users without a Apps-Domain (Sheets-Copie to tryscript.com?)
It's a help for JS-Beginners like me.

Thank you.

Did you find this answer helpful?

Report abuse

NoodleGei Level 5 6/19/09

For your Sample, this make my own Spreadsheet-Copy in tryscript.com:

http://spreadsheets.google.com/a/tryscript.com/ccc?key=r7PRNG-DcO3Qk33l8cVgYCQ&newcopy

0 of 1 people found this answer helpful. Did you?

Report abuse

craignm Level 1 6/10/10

JR, I suspect the HTML of the Google Result page has changed since you posted this. I updated the parsing code by replacing:

  var tableSpot = s.indexOf('<table');
  tableSpot = s.indexOf('<table',tableSpot+5);
  tableSpot = s.indexOf('<table',tableSpot+5);
  var equalSign = s.indexOf(' = ',tableSpot);

with:

  var calculatorResponse = s.indexOf('<h2 class=r');
  var equalSign = s.indexOf(' = ',calculatorResponse);
  var firstSpace = s.indexOf(' ', equalSign+3);

-- i.e. look for "<h2 class=r" instead of the third "<table"

Also, my spreadsheet included results from GoogleLookup for aircraft ranges, and used several units: km, miles, nautical miles, e.g.

5467 km
3,000 miles
3430miles
6,325 nautical miles
...

I therefore created a new function that assumes fromUnits is part of fromQty:

function convertTo(fromQty, toUnits) {
  var q = 'http://www.google.com/search?q='+ fromQty + '+in+' + toUnits;

It works like a charm (and saved a *lot* of time). Thanks for the example function!

craig

PS one other trick: calculator doesn't like "2345 km in km", so I converted everything to meters (none of the measurements were in meters), and then back to kilometers by simple division.