Google Sheets - Basic Script

I believe your goal as follows.

• You want to retrieve the values from URLs at the column "B" using the custom function.

• The screen shot of your sample Spreadsheet is as follows.

  

• Your current script is as follows. (This script is from your sample Spreadsheet.)

    function AsxPrice() {
      var url = SpreadsheetApp.getActiveSheet().getRange('B2').getValue();
      var response = UrlFetchApp.fetch(url);
      var content = response.getContentText();
      Logger.log(content);
      var json = JSON.parse(content);
      var last_price = json["last_price"];
      return last_price;
    }
  

In your script, the cell range is constant like B2. So in this answer, I would like to propose to retrieve the URLs as the arguments for the custom function. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And in this custom function, when your sample Spreadsheet is used, please put the formula of =AsxPrice2(B2:B4) to a cell. By this, the URLs are retrieved from the cells "B2:B4" and retrieve the values.

function AsxPrice2(values) {
  const reqs = values.flat().map(url => ({url: url, muteHttpExceptions: true}));
  return UrlFetchApp.fetchAll(reqs).map(r => [r.getResponseCode() == 200 ? JSON.parse(r.getContentText()).last_price : ""]);
}

Result:

When above scvript is used for your sample Spreadsheet, it becomes as follows.

Note:

• When a lot of URLs are used, an error might occur. Please be careful this.

References:

• Custom Functions in Google Sheets
• fetchAll(requests)

Solution

You only have to modify your function to have an input parameter which is the url

Code

  function AsxPrice(url) {
    var response = UrlFetchApp.fetch(url);
    var content = response.getContentText();
    Logger.log(content);
    var json = JSON.parse(content);
    var last_price = json["last_price"];
    return last_price;
  }

References:

• Custom functions