错误:“参考不存在"使用自定义功能时 [英] Error: "Reference does not exist" when using a custom function

问题描述

我正在尝试抓取网页&将该值放入缓存中，以便不超过每日的urlFetch限制.

I'm trying to scrap a webpage & to put the value in cache in order to not hit the daily urlFetch limit.

这是我正在使用的代码，它可以在不使用Cache&属性服务，但是当我尝试添加该元素时不行.

This is the code I'm using, it works without the Cache & Properties service but not when I try to add that element.

function scrapercache(url) {
  var url = "https://www.gurufocus.com/term/fscore/nyse:ABBV/Piotroski-F-Score";
  var result = [];
    var description;
    var options = {
        'muteHttpExceptions': true,
        'followRedirects': false,
    };
Logger.log('line 16 OK');
  
var cache = CacheService.getScriptCache();
var properties = PropertiesService.getScriptProperties();
Logger.log('line 21 OK');
 
  let res = cache.get(url);
//  if(res){
 // return JSON.parse(res)
//}
Logger.log(res);
Logger.log('line 24 OK');

  if (res) {
    // trim url to prevent (rare) errors
    url.toString().trim();
    var r = UrlFetchApp.fetch(url, options);
    Logger.log(r);
    Logger.log('line 34 OK');
    
    var c = r.getResponseCode();
    Logger.log(c);
    Logger.log('line 38 OK');

    // check for meta refresh if 200 ok
    if (c == 200) {
      var html = r.getContentText();
      cache.put(url, "cached", 21600);
      properties.setProperty(url, html);
      Logger.log('line 46 OK');

      var $ = Cheerio.load(html); // make sure this lib is added to your project!
      Logger.log('line 49 OK');

      // meta description
      if ($('meta[name=description]').attr("content")) {
        description = $('meta[name=description]').attr("content").trim();
        var trim_des = description.substr(0, 40);
        Logger.log('line 55 OK');
      }
    }
  
    result.push([trim_des]);   
    Logger.log('line 60 OK');
  }

  return result;
  Logger.log('line 64 OK');
}

我这样调用函数:

=scrapercache("https://www.gurufocus.com/term/fscore/nyse:ABBV/Piotroski-F-Score")

&我收到以下错误消息

& I get the following error message

错误:引用不存在

Error: Reference does not exist

我添加了日志行，以查看脚本是否正确处理了&直到28岁之前看起来还可以

I added log lines to see if the script was processing correctly & it looks like it's ok only until like 28

推荐答案

错误:引用不存在"

通常通过调用电子表格单元格中的自定义函数返回此错误消息，该电子表格单元格不返回值. 官方文档明确提到了该错误，但错误消息未提供，因此混乱是可以理解的.

This error message is usually returned by calling a custom function in a spreadsheet cell that does not return a value. It is explicitly mentioned by the official docs, but the error message is not provided, so the confusion is understandable.

空数组不是有效的返回值(因为没有要返回的元素).使用以下自定义函数可以轻松重现该错误:

An empty array is not a valid return value (since there are no elements to return). The error is easily reproducible with the following custom function:

/**
 * @customfunction
 */
function testReferenceError() {
  const list = [];

  return list;
}

当在单元格中被调用时，这导致期望的"请求.错误:

Which, when called in a cell, resulting in the "desired" error:

适用于您的情况

根据您的情况，当cache中存在缓存的数据时，if语句子句的计算结果为false(

In your situation, when there is a cached data in cache, the if statement clause evaluates to false (truthy value when negated evaluates to false). When it does, nothing gets pushed to the result, and an empty array is returned in finally (see above for the explanation of consequences). Consider this mock:

const cache = {
  get() {
    return "cached";
  }
};

let res = cache.get("mock_url");

//ternary operator here acts the same as "if...else":
console.log( !res ? "will enter if block" : "will enter else block" );

最后在return上的注释:如果将return语句放入finally块中，则

Note on return in finally: If you put a return statement into a finally block, expect it to override the return statements in try or catch. Consider this example close to how your program is structured:

const final = (add = false) => {
  const list = [];
  
  try {
    add && list.push(1);
   
    return [1,2]; //this return is skipped
  }
  catch(error) {
    list.push(error);
  }
  finally {
    return list;
  }
  
};

console.log( final() );

此外，问题已经在这里有答案

这篇关于错误:“参考不存在"使用自定义功能时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！