Google Sheet Script Returning #NAME?

Question

I have set up a script to pull in data from a JSON API into a Google Sheet. I have set it to refresh by adding a third parameter which isn't used in the API call but is linked to a cell which another script adds the current time to. This ensures that the API is called regularly.

We are then using this Google Sheet to input data into Google Ads.

It all seems to function correctly, however, when the sheet has been closed for a while (e.g. overnight) and Google Ads tries to update from the sheet, it imports #NAME? instead of the correct API value.

I have set up another script which records the API values at regular intervals. This seems to record the values correctly, suggesting that the API calls are working whilst the sheet is closed.

// Make a POST request with a JSON payload.
// Datetime parameter isn't use in API call but is used to refresh data

function TheLottAPI(game,attribute,datetime) {

var data = {
  'CompanyId': 'GoldenCasket',
  'MaxDrawCount': 1,
  'OptionalProductFilter': [game]};

    Logger.log(data);  

var options = {
  'method' : 'post',
  'contentType': 'application/json',
  // Convert the JavaScript object to a JSON string.
  'payload' : JSON.stringify(data)};

  var response = UrlFetchApp.fetch('https://data.api.thelott.com/sales/vmax/web/data/lotto/opendraws', options);

  Logger.log('output: '+ response);          

  // Convert JSON response into list
  var json = JSON.parse(response)
 var drawList=json ["Draws"];

  // Extract attribute from list
 for(var i=0;i<drawList.length;i++)
{var value=drawList[i][attribute];} 
  Logger.log(value)

  return value;
  SpreadsheetApp.flush();
};


// Set date & time to refresh API call

function RefreshTime() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attributes").getRange("K4").setValue(new Date().toTimeString());
}

The correct numeric values from the API should be shown, rather than the #NAME? error.

I have checked that the API call is functioning correctly by using another script to copy the current values. The API was updating at the appropriate times overnight.

 function RecordDraws() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Draw Amounts");
  var source = sheet.getRange("A3:D3");
  var values = source.getValues();

  values[0];
  sheet.appendRow(values[0]);
  };

Rubén Rubén · Accepted Answer · 2019-07-12T00:17:28

This is my guess

Google Sheets custom functions definitions are loaded when the spreadsheet is opened by using the Google Sheets UI, then formulas are calculated and as custom functions are already defined they are calculated correctly. If the spreadsheet isn't opened this way the custom functions definitions aren't loaded thus the spreadsheet doesn't know what to do with that function and returns #NAME?

If you are already running a script that updates some values, enhance that script to do the calculations that does your custom function.

Google Sheet Script Returning #NAME?

2 Answers