0

I'm setting up a spreadsheet that pulls data from an API when given a string. This requires a custom function to pull and parse the API. I would like to use one instance of the formula to cover the entire spreadsheet, but adapting the Google example code:

function DOUBLE(input) {
  if (input.map) {            // Test whether input is an array.
    return input.map(DOUBLE); // Recurse over array if so.
  } else {
    return input * 2;
  }
}

doesn't return any values.

This is my current code. The code itself will not work without an API key from the website in question to replace my 'PRIVATE' authorization token.

function COASTER(input) {

 /**
 * Returns coaster stats from name.
 *
 * @param {name} input The value or range of cells to use.
 * @return Coaster statistics.
 * @customfunction
 */

  if (input.map) {            // Test whether input is an array.
    return input.map(COASTER); // Recurse over array if so.
  } else {

    // Call the Captain Coasters API
    var response = UrlFetchApp.fetch("https://captaincoaster.com/api/coasters?name=" + input ,{"headers": {'X-Auth-Token': 'PRIVATE' }});

    // Parse the JSON reply
    var json = response.getContentText();
    var data = JSON.parse(json);

    var results = data["hydra:member"];
    var output = []

    results.forEach(function(elem) {
      output.push([elem["@type"],elem["materialType"]["name"],elem["length"],elem["speed"],elem["manufacturer"]["name"],elem["seatingType"]["name"],elem["inversionsNumber"],elem["height"],elem["status"]["name"],elem["park"]["name"],elem["score"],elem["rank"]]);
    });
    return output
  }
}

I expected the values produced by the formula COASTER(B2:B3) to exist, but I get the following error: TypeError: Cannot read property "map" from undefined. at COASTER(Captain Coaster API:11), line 11 being the beginning of the if statement.

Doyle Rudolph
  • 5
  • 3
  • Writing the push to a new array solved my row issue. I've edited the post to show the remaining conundrum I have. – Doyle Rudolph Aug 07 '19 at 22:45
  • 1
    Can you share a copy of your sheet? Are any cells in `B2:B3` empty; if not, what are their values? Have you tried running this with values from a test function (e.g., simply making a `function test() { Logger.log(COASTER('test input')); }`)? Also, fair warning, [updating values from a custom function will cause some other issues](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet); you might find it easier to just do things in GAS on a trigger. – sinaraheneba Aug 07 '19 at 23:27

0 Answers0