Google表格脚本返回"#NAME"吗? [英] Google Sheet Script Returning #NAME?

问题描述

我已经设置了一个脚本，可以将数据从JSON API提取到Google表格中.我通过添加第三个参数来刷新它，该参数在API调用中未使用，但链接到另一个脚本将当前时间添加到的单元格.这样可以确保定期调用API.

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.

然后我们将使用此Google表格将数据输入到Google Ads.

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

这一切似乎都能正常运行，但是，当工作表关闭了一段时间(例如整夜)并且Google Ads尝试从工作表进行更新时，它会导入#NAME吗?而不是正确的API值.

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.

我已经设置了另一个脚本，该脚本定期记录API值.这似乎正确记录了这些值，表明关闭工作表时API调用正在工作.

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

应该显示来自API的正确数值，而不是#NAME?错误.

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

我已经通过使用另一个脚本复制当前值来检查API调用是否正常运行.该API将在一夜之间的适当时间进行更新.

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

推荐答案

尝试将其转换

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

对此:

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

因为关闭电子表格或从API调用该方法时，我认为没有活动表格".

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

这篇关于Google表格脚本返回"#NAME"吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！