Ошибка ссылка не существует гугл таблицы

I am writing a function in Google Apps Script and it seems the last error I need to get around is a «reference does not exist» error in Google Sheets when I call my function. I don’t know what to do about this because it doesn’t seem to be a problem with my code.

This is what my code looks like now. It isn’t complete because I need to change it for user input, but this is a test.

In a google sheets cell I type in =sortingtesting()

function sortingtesting() 
{
  var pInfo1 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pInfo2 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pInfo3 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pWO = ['1','','','2','','','3','4','5','6','7','','','8','','','9','10']
  var pSearch = ['c', 'b', 'a']

  var WO = [];
  var Info1 = [];
  var Info2 = [];
  var Info3 = [];
  var Search = [];
  
  for(var i = 0; i < 18; i++)
    WO[i] = pWO[i];
  
  for(var i = 0; i < 18; i++)
  {
      Info1[i] = pInfo1[i];
  }
  
  for(var i = 0; i < 18; i++)
  {
      Info2[i] = pInfo2[i];
  }
  
  for(var i = 0; i < 18; i++)
  {
      Info3[i] = pInfo3[i];
  }
  
  for(var i = 0; i < 1; i++)
    Search[i] = pSearch[i];
  
  // Declares secondary storage arrays and their counters
  var FinalArray1 = [];
  var FinalArray2 = [];
  var FinalArray3 = [];
  var LastArray = [];
  var a = 0;
  var b = 0;
  var c = 0;
  var d = 0;
  
  // loop to run and make all of the cells in the work order row relevant to the work order number
  for(var row = 0; row < WO.length; row ++)
  {
    var counter = row - 1;
    while(WO[row] == "")
    {
      WO[row] = WO[counter];
      counter--;
    }
  }
  
  // loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array

    for(var row = 0; row < Info1.length; row++)
    {
      if(Info1[row] == Search[0])
      {   
        FinalArray1[a] = WO[row];
        a++;
      }
    }
  
  
   
    for(var row = 0; row < Info1.length; row++)
    {
      if(Info2[row] == Search[1])
      {   
        FinalArray2[b] = WO[row];
        b++;
      }
    }
  
  

    for(var row = 0; row < Info1.length; row++)
    {
      if(Info3[row] == Search[2])
      {   
        FinalArray3[c] = WO[row];
        c++;
      }
    }
  
  
  // loop to run through and get all the work orders that meet all of the criteria
  for(var i = 0; i < FinalArray1.length; i++)
  {
    for(var j = 0; j < FinalArray2.length; j++)
    {
      for(var k = 0; k < FinalArray3.length; k++)
      {
        if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
        {
          LastArray[d] = FinalArray1[i];
          d++;
        }
      }
    }
  }
  
  return LastArray;
}

Solution Found:
This is my working code with arrays coming in from google sheets as parameters and I just thought it would be nice to put the working prototype out there:

function sortingtesting(WO, Info, Search) 
{ 
  // Declares secondary storage arrays and their counters
  var FinalArray1 = [];
  var FinalArray2 = [];
  var FinalArray3 = [];
  var LastArray = [];
  var a = 0;
  var b = 0;
  var c = 0;
  var d = 0;
  
  // loop to run and make all of the cells in the work order row relevant to the work order number instead of being blank
  for(var row = 0; row < WO.length; row ++)
  {
    var counter = row - 1;
    while(WO[row] == "")
    {
      WO[row] = WO[counter];
      counter--;
    }
  }
  
  // loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array to store the work orders that meet the criteria
  for(var col = 0; col < Info[0].length; col++)
  {
    for(var row = 0; row < Info.length; row++)
    {
      if(Info[row][col] == Search[0])
      {   
        FinalArray1[a] = WO[row];
        a++;
      }
      else if(Info[row][col] == Search[1])
      {
        FinalArray2[b] = WO[row];
        b++;
      }
      else if(Info[row][col] == Search[2])
      {
        FinalArray3[c] = WO[row];
        c++;
      }
    }
  }
  
  LastArray[0] = 'N/A';
  
  // loop to run through and get all the work orders that meet all of the criteria
  for(var i = 0; i < FinalArray1.length; i++)
  {
    for(var j = 0; j < FinalArray2.length; j++)
    {
      for(var k = 0; k < FinalArray3.length; k++)
      {
        if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
        {
          LastArray[d] = FinalArray1[i];
          d++;
        }
      }
    }
  }
  
  return LastArray;
}

I am writing a function in Google Apps Script and it seems the last error I need to get around is a «reference does not exist» error in Google Sheets when I call my function. I don’t know what to do about this because it doesn’t seem to be a problem with my code.

This is what my code looks like now. It isn’t complete because I need to change it for user input, but this is a test.

In a google sheets cell I type in =sortingtesting()

function sortingtesting() 
{
  var pInfo1 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pInfo2 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pInfo3 = ['a','b','c','d','e','f','g','h','i','j','k','l','m','o','p','q','r','s']
  var pWO = ['1','','','2','','','3','4','5','6','7','','','8','','','9','10']
  var pSearch = ['c', 'b', 'a']

  var WO = [];
  var Info1 = [];
  var Info2 = [];
  var Info3 = [];
  var Search = [];
  
  for(var i = 0; i < 18; i++)
    WO[i] = pWO[i];
  
  for(var i = 0; i < 18; i++)
  {
      Info1[i] = pInfo1[i];
  }
  
  for(var i = 0; i < 18; i++)
  {
      Info2[i] = pInfo2[i];
  }
  
  for(var i = 0; i < 18; i++)
  {
      Info3[i] = pInfo3[i];
  }
  
  for(var i = 0; i < 1; i++)
    Search[i] = pSearch[i];
  
  // Declares secondary storage arrays and their counters
  var FinalArray1 = [];
  var FinalArray2 = [];
  var FinalArray3 = [];
  var LastArray = [];
  var a = 0;
  var b = 0;
  var c = 0;
  var d = 0;
  
  // loop to run and make all of the cells in the work order row relevant to the work order number
  for(var row = 0; row < WO.length; row ++)
  {
    var counter = row - 1;
    while(WO[row] == "")
    {
      WO[row] = WO[counter];
      counter--;
    }
  }
  
  // loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array

    for(var row = 0; row < Info1.length; row++)
    {
      if(Info1[row] == Search[0])
      {   
        FinalArray1[a] = WO[row];
        a++;
      }
    }
  
  
   
    for(var row = 0; row < Info1.length; row++)
    {
      if(Info2[row] == Search[1])
      {   
        FinalArray2[b] = WO[row];
        b++;
      }
    }
  
  

    for(var row = 0; row < Info1.length; row++)
    {
      if(Info3[row] == Search[2])
      {   
        FinalArray3[c] = WO[row];
        c++;
      }
    }
  
  
  // loop to run through and get all the work orders that meet all of the criteria
  for(var i = 0; i < FinalArray1.length; i++)
  {
    for(var j = 0; j < FinalArray2.length; j++)
    {
      for(var k = 0; k < FinalArray3.length; k++)
      {
        if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
        {
          LastArray[d] = FinalArray1[i];
          d++;
        }
      }
    }
  }
  
  return LastArray;
}

Solution Found:
This is my working code with arrays coming in from google sheets as parameters and I just thought it would be nice to put the working prototype out there:

function sortingtesting(WO, Info, Search) 
{ 
  // Declares secondary storage arrays and their counters
  var FinalArray1 = [];
  var FinalArray2 = [];
  var FinalArray3 = [];
  var LastArray = [];
  var a = 0;
  var b = 0;
  var c = 0;
  var d = 0;
  
  // loop to run and make all of the cells in the work order row relevant to the work order number instead of being blank
  for(var row = 0; row < WO.length; row ++)
  {
    var counter = row - 1;
    while(WO[row] == "")
    {
      WO[row] = WO[counter];
      counter--;
    }
  }
  
  // loop that goes through saving which work orders meet certain search criteria, each search criteria has its own separate secondary array to store the work orders that meet the criteria
  for(var col = 0; col < Info[0].length; col++)
  {
    for(var row = 0; row < Info.length; row++)
    {
      if(Info[row][col] == Search[0])
      {   
        FinalArray1[a] = WO[row];
        a++;
      }
      else if(Info[row][col] == Search[1])
      {
        FinalArray2[b] = WO[row];
        b++;
      }
      else if(Info[row][col] == Search[2])
      {
        FinalArray3[c] = WO[row];
        c++;
      }
    }
  }
  
  LastArray[0] = 'N/A';
  
  // loop to run through and get all the work orders that meet all of the criteria
  for(var i = 0; i < FinalArray1.length; i++)
  {
    for(var j = 0; j < FinalArray2.length; j++)
    {
      for(var k = 0; k < FinalArray3.length; k++)
      {
        if(FinalArray3[k] == FinalArray2[j] && FinalArray2[j] == FinalArray1[i])
        {
          LastArray[d] = FinalArray1[i];
          d++;
        }
      }
    }
  }
  
  return LastArray;
}

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")

2 ответа

let cached = cache.get(url)
if(cached){
  return JSON.parse(cached)
}

let results =  ["blah","blah"] // stuff we got with cheerio
cache.put(url, JSON.stringify(results), 120)
return results

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

  return list;
}

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" );

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

Только недавно начали работать с Google Sheets и вдруг появляется #ERROR? Ничего страшного, такое случается время от времени, и это не значит, что проблему нельзя исправить.

Есть ли способ исправить ошибку разбора формулы в Google Sheets

Если вы давно работаете в Excel или Google Sheets, то наверняка сталкивались с ошибками при работе в этих приложениях. На самом деле, ошибки возникают в основном из-за неправильных формул или невнимательности при работе с таблицами. Однако существует и ряд других причин, которые могут спровоцировать такие ошибки. Они перечислены ниже.

Однако если у вас возникла проблема с Google Sheets, первое, что вы должны сделать, — понять причину такого сценария. И здесь вы найдете как причины возникновения ошибок, так и их определение, а значит, и методы устранения.

Каковы причины возникновения проблемы

Начнем с того, что основными причинами ошибок при разборе являются либо невнимательность, либо какие-то другие причины, которые можно перечислить ниже:

• Вы пытаетесь разобрать данные из несуществующего файла.
• В данных, которые вы пытаетесь разобрать, возникла ошибка. Это может произойти при загрузке файла, содержащего данные для разбора. Если дело обстоит именно так и загрузка файла вызвала ошибку разбора, вы можете попробовать загрузить файл еще раз или поискать обновленный файл. Вы также можете попробовать загрузить файл с другого сайта, если это возможно.
• Данные для разбора файла могут быть несовместимы с используемой операционной системой или программой. Обязательно проверьте это перед загрузкой файла.
• Разрешения могут быть недостаточными или те, которые позволяют вам получить доступ к данным файла, еще не были предоставлены. Запросите необходимые разрешения и, если они будут предоставлены, повторите попытку анализа данных.
• Не хватает места на диске, необходимого для разбора, в результате чего возникает ошибка разбора. При записи файла на жесткий диск или USB убедитесь, что на диске достаточно места для результатов разбора. Вы также можете переместить разбираемый файл или запустить его на жесткий диск, если разбор выполняется со съемного носителя.

Какие ошибки разбора могут возникать и как с ними бороться

Итак, вы уже догадались, каковы могут быть причины этих ошибок, но стоит отметить наиболее популярные синтаксические ошибки. Последние в основном вызваны невнимательностью и вставкой ненужных символов. Вот некоторые из этих ошибок:x

• Ошибка #Error. Сообщение #ERROR! является уникальным для Google Sheets и означает, что он не может понять введенную формулу и поэтому не может выполнить команду для анализа данных.
  • Чтобы избежать сообщения #ERROR! при разборе, необходимо убедиться, что формула написана правильно, и исправить все найденные синтаксические ошибки. Обязательно тщательно проверьте формулу на наличие неточностей или ошибок в самой формуле.
  • Это также происходит, если вы пропустили символ «&» при соединении текстовых и числовых значений. Формула должна выглядеть так: =»Итого»&sum(A1:A6), которая отображается как Total21 (числа в A1-A6, сложенные вместе).

  • Дополнительная скобка также может вызвать ошибку.
• Ошибка #DIV/0. Если вы непреднамеренно пытаетесь разделить на 0, вы получите ошибку #DIV/0. Дважды проверьте свои формулы и убедитесь, что вы не пытаетесь делить на ноль.
• Ошибка #N/A. Если возникает ошибка #N/A Error, это обычно означает, что значение, на которое делается ссылка, не существует или было указано неверно. Убедитесь, что вы ввели правильные ячейки и значения.
• Ошибка #NUM! Ошибка. Если вы используете формулу, которая содержит недопустимые числа, то вы можете увидеть ошибку #NUM! Ошибка. Во избежание осложнений просмотрите свои формулы перед разбором данных и исправьте все нарушения, которые вы можете обнаружить.

Что поможет написать/разобрать/исправить сложную формулу

Принцип лука — так назвал этот подход эксперт по луку Бен Коллинз. Идея заключается в следующем: вместо того чтобы писать сложную формулу прямо, мы сначала пишем часть, смотрим, как она работает, и если она правильно вычисляет промежуточное значение, то ссылаемся на эту формулу из другой формулы, в которой она будет одним из аргументов, и если все работает, то удаляем столбец с промежуточной формулой и переносим ее в конечную формулу (в то место, где мы на нее ссылались).

Другими словами, мы формируем отдельные части формулы в отдельных ячейках, ссылаясь со следующей на предыдущую, а затем объединяем их в одну.

Теперь вы знаете больше о том, как предотвратить или устранить проблему в Google Sheet, как отредактировать или переписать формулу по принципу лука и как не допустить ошибку в следующий раз. И даже если вы допустите ошибку, это укрепит ваши знания о Google Sheets. Экспериментируйте и не бойтесь совершать новые ошибки!

IMPORTRANGE() appears to be the function you want.

From the Google spreadsheets function list:

Google Spreadsheets lets you reference
another workbook in the spreadsheet
that you’re currently editing by using
the ImportRange function. ImportRange
lets you pull one or more cell values
from one spreadsheet into another. To
create your own ImportRange formulas,
enter =importRange(spreadsheet-key,
range). For languages where comma is
used for decimal separation, use a
semicolon instead of a comma to
separate arguments in your formula.

Spreadsheet-key is a STRING which is
the key value from the spreadsheet
URL.

Range is a STRING representing the
range of cells you want to import,
optionally including the sheet name
(defaults to first sheet). You can
also use a range name if you prefer.

Given that the two arguments are
STRINGs, you need to enclose them in
quotes or refer to cells which have
string values in them.

For example:

=importrange(«abcd123abcd123», «sheet1!A1:C10») «abcd123abcd123» is
the value in the «key=» attribute on
the URL of the target spreadsheet and
«sheet1!A1:C10» is the range which is
desired to be imported.

=importrange(A1,B1) Cell A1 contains the string ABCD123ABCD123 and cell B1
contains sheet1!A1:C10

Note: In order to use ImportRange, you
need to have been added as a viewer or
collaborator to the spreadsheet from
which ImportRange is pulling the data.
Otherwise, you’ll get this error:
«#REF! error: The requested
spreadsheet key, sheet title, or cell
range was not found.»

«key» is, of course, the string in the URL for the spreadsheet that matches to the key= parameter.

I just tested it by creating two spreadsheets. In cell A1 of the first, I put a string. In cell A1 of the second, I put =importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and it displayed the string from the first spreadsheet. (Your key will obviously be different.)

(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you’ll need to replace it with a semi-colon: =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))

NOTES:

1. Google currently sets a hard limit of 50 «cross-workbook reference formulas» per spreadsheet. Source: Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer)

2. The «new» Google Spreadsheet (soon to be the standard) removes the 50 limit of 50 «cross-workbook reference formulas (Google Support) (h/t Jacob Jan Tuinstra)

3. In the «new» Google Sheets you also use the entire URL as the key (Google Support) (h/t Punchlinern)

IMPORTRANGE() appears to be the function you want.

From the Google spreadsheets function list:

Google Spreadsheets lets you reference
another workbook in the spreadsheet
that you’re currently editing by using
the ImportRange function. ImportRange
lets you pull one or more cell values
from one spreadsheet into another. To
create your own ImportRange formulas,
enter =importRange(spreadsheet-key,
range). For languages where comma is
used for decimal separation, use a
semicolon instead of a comma to
separate arguments in your formula.

Spreadsheet-key is a STRING which is
the key value from the spreadsheet
URL.

Range is a STRING representing the
range of cells you want to import,
optionally including the sheet name
(defaults to first sheet). You can
also use a range name if you prefer.

Given that the two arguments are
STRINGs, you need to enclose them in
quotes or refer to cells which have
string values in them.

For example:

=importrange(«abcd123abcd123», «sheet1!A1:C10») «abcd123abcd123» is
the value in the «key=» attribute on
the URL of the target spreadsheet and
«sheet1!A1:C10» is the range which is
desired to be imported.

=importrange(A1,B1) Cell A1 contains the string ABCD123ABCD123 and cell B1
contains sheet1!A1:C10

Note: In order to use ImportRange, you
need to have been added as a viewer or
collaborator to the spreadsheet from
which ImportRange is pulling the data.
Otherwise, you’ll get this error:
«#REF! error: The requested
spreadsheet key, sheet title, or cell
range was not found.»

«key» is, of course, the string in the URL for the spreadsheet that matches to the key= parameter.

I just tested it by creating two spreadsheets. In cell A1 of the first, I put a string. In cell A1 of the second, I put =importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and it displayed the string from the first spreadsheet. (Your key will obviously be different.)

(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you’ll need to replace it with a semi-colon: =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))

NOTES:

1. Google currently sets a hard limit of 50 «cross-workbook reference formulas» per spreadsheet. Source: Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer)

2. The «new» Google Spreadsheet (soon to be the standard) removes the 50 limit of 50 «cross-workbook reference formulas (Google Support) (h/t Jacob Jan Tuinstra)

3. In the «new» Google Sheets you also use the entire URL as the key (Google Support) (h/t Punchlinern)