Google Sheets script intermittent error showing #NAME?

0
votes

I have a script on Google Sheets that is scheduled to run hourly. The script runs successfully sometimes but it fails with a #NAME? error other times.

All the script does is copy a value in a cell and appends that to a list of values previous values. The problem is that the copied cell is populated from a website API via a JSON call. I think I need a way for the first script to wait until the cell is populated but don't know how. 

function recordHistory() {
SpreadsheetApp.flush();
Utilities.sleep(99000);  //just trying a long wait to see if that works
var ss = SpreadsheetApp.getActiveSpreadsheet();
var portfolioSheet = ss.getSheetByName("Portfolio");
var historySheet = ss.getSheetByName("Portfolio History");
var data = [      
    Date(),
    portfolioSheet.getRange('C36').getValue(),
    portfolioSheet.getRange('H36').getValue(),
    portfolioSheet.getRange('A100').getValue(),
    'plaintext'
]
historySheet.appendRow(data);
};

Any thoughts?

1
google-apps-scriptgoogle-sheets
Welcome to StackOverFlow please take this opportunity to take the tour and learn how to How to Ask, format code and minimal reproducible example. Google Apps Script Documentation. – Cooper
We can't really help you unless we can generate the problem and the problem isn't generated by the code you've given us. So you haven't provide us with a minimal reproducible example. – Cooper
Are you having something like =importdata() function in your spreadsheet? – Anees Hameed

1 Answers

1
votes

You could try making the API calls and getting the response before copying the values

Example:

function forTheBooks() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var portfolioSheet = ss.getSheetByName('Portfolio');
  var historySheet = ss.getSheetByName("Portfolio History");

  // Make API call & put values in "Portfolio" sheet.
  var api_content = UrlFetchApp.fetch('https://restframework.herokuapp.com/users/?format=json').getContentText();
  portfolioSheet.getRange('A1').setValue(api_content);

  // Make another API call & put more values in "Portfolio" sheet.
  api_content = UrlFetchApp.fetch('https://restframework.herokuapp.com/snippets/?format=json').getContentText();
  portfolioSheet.getRange('A2').setValue(api_content);

  var data = [      
    Date(),
    portfolioSheet.getRange('A1').getValue(),
    portfolioSheet.getRange('A2').getValue(),
    'plaintext'
  ]
  historySheet.activate().appendRow(data);
}

This way the code that copies that values to the history sheet actually waits for the response of the API first.