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]);
  };

score 0 · Answer 1 · answered Jul 12 '19 at 00:17

0

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.

answered Jul 12 '19 at 00:17

Rubén

34,714
9
70
166

As far as I know, formulas keep working when the Spreadsheed is closed. I even added formulas from a script and another one still gets the correct value. I'm still investigating this. – Jescanellas Jul 12 '19 at 09:35
Thanks for the suggestions. I can confirm that the formulas continue to work when the sheet is closed, as the script that records values has been working correctly and recording the API values which change overnight. I think it's more a problem to do with Google Ads accessing the sheet, perhaps it's not giving the sheet long enough to load properly. – apit Jul 14 '19 at 23:23
@apit Please update your question to include how do you to checked that the formulas including custom functions continue to work when the sheet is closed. – Rubén Jul 14 '19 at 23:57
@Rubén I have added it at the end of the question. It records the values of the cells which feature the API custom function is used and adds it as a new row below. – apit Jul 15 '19 at 01:10

score 0 · Answer 2 · answered Jul 12 '19 at 09:51

0

Try converting this

 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attributes").getRange("K4").setValue(new Date().toTimeString());

Into this:

 SpreadsheetApp.openById("id").getSheetByName("Attributes").getRange("K4").setValue(new Date().toTimeString());

Because I don't think there is an "active sheet" when the Spreadsheet it's closed or the method is called from the API.

answered Jul 12 '19 at 09:51

Jescanellas

2,555
2
9
20

2

The formula is =TheLottAPI("Powerball","Div1Amount",Attributes!K4) It works fine when the sheet is open & closed, just not when Google Ads tries to access the sheet. – apit Jul 16 '19 at 00:02

Google Sheet Script Returning #NAME?

2 Answers2

Linked