-2

I am trying to understand why a custom google sheet script can initially pull in JSON data when I have the sheet open, as defined in cell(A1).

20 minutes after closing the google sheet in the browser, I access the published html page of that sheet and the data is not displayed.

I wanted to see a proof of concept for a google-apps-script function that is run within google sheets and fetches a JSON page and displays the data as a google sheets table in html.

I went through this tutorial which gives an example function https://www.youtube.com/watch?v=EXKhVQU37WM&t=2s

* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
  
  try{
    // /rates/EUR
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    
    var patharray = xpath.split("/");
    //Logger.log(patharray);
    
    for(var i=0;i<patharray.length;i++){
      json = json[patharray[i]];
    }
    
    //Logger.log(typeof(json));
    
    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];
      
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data";  
  }
  
}

When I open the sheet in cell A1 the function IMPORTJSON is called. I get an error for around 5 seconds("error, unknown function") and then the data is fetched and correctly displayed.

When I go to the link of the published sheet page as html (file -> share -> publish to web) 20 minutes after closing the sheet, I get a #NAME? error in the cell with the function.

What the error looks like in chrome. enter image description here

google doc: https://docs.google.com/spreadsheets/d/1iDuaKn5S6jnRJmGW6iENvQ6-cE5BK-R-ZEnIVBPJc5w/edit?usp=sharing

html published page https://docs.google.com/spreadsheets/d/e/2PACX-1vRllWzf7g-lARy_PgRUNHc0Jz1AB9W8nN0tmfvLQzE4rpwq3j3C7DwiD154K6_UeilDUpkLSGO8UIJT/pubhtml?gid=0&single=true

Here is the function as defined in the Apps Script for the sheet https://www.chicagocomputerclasses.com/google-sheets-import-json-importjson-function/

Function call(cellA1) (XXX is API key):

=IMPORTJSON("http://data.fixer.io/api/latest?access_key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX&symbols=USD,AUD,CAD,PLN,MXN&format=1","rates")

Why can't the data pulled by the function(when the sheet is open in the browser) be static on the sheet when the google sheet is closed, and the html version of the sheet is accessed?

I haven't called the function in the meantime by accessing the google sheet.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Putnik
  • 45
  • 2
  • 9
  • Whenever possible, try to include a [minimal example](https://stackoverflow.com/help/minimal-reproducible-example) that reproduces the issue. You can also include the expected behavior, the actual behavior, and how they differ, that would be helpful as well. Please visit [How to ask](https://stackoverflow.com/help/how-to-ask) to have some tips on how to write a question, so the community will be able to help you out in a better way – Gabriel Carballo Feb 08 '22 at 22:50
  • Hi! Besides the comment above, here's a couple of points that may help you get an swer. The file whose link is above is available as an ```html``` page. The file should have been shared and not published. That will help others see the problem more clearly. As to automatic update, that shoould be a new question. Cheers. – onit Feb 09 '22 at 02:28
  • Sounds good. I will rewrite the question and post example code – Putnik Feb 09 '22 at 03:16
  • I rewrote the comment by adding code, the tutorial where I found the code and a link to the sheet. I hope my question is much clearer by now – Putnik Feb 09 '22 at 03:35

1 Answers1

0

Unfortunately, this is a bug in the google sheet framework. Loading the html version of a sheet does not allow the sheet to call custom functions.

https://issuetracker.google.com/issues/218993622

Putnik
  • 45
  • 2
  • 9