Google Sheets Script .getValue() and .getDisplayValue() returning #REF For Some Reason

1
votes

I had this function working before, but for some reason it is not working anymore. This is a picture of the three pieces of data I am trying to collect. They have references to a couple other sheets in my file including some 'INDIRECT's. The following is the simple code I am trying to run to obtain the value of one of the pieces of data:

var ss = SpreadsheetApp.openById(logKey).getSheetByName("Investing");
var value = ss.getRange("G2").getValue();
var formula = ss.getRange("G2").getFormula();
Logger.log("Value: " + value + "  Formula: " + formula);

... And this is the output:

[16-10-06 09:50:04:531 EDT] Value: #REF! Formula: =AD$2

As you can see from the output, it is successfully reading the cell from the spreadsheet, but it is not obtaining the value from the cell, just the formula. I don't understand why this is the case as in my sheet, the value it is trying to obtain is not '#REF!'.

As I said before, I had this general piece of code working before, but I must have changed something such that it broke what I am attempting to do.

Additional note: I am unsure if this code may be the culprit for some reason, even though it worked like this before. This is simply to convert columns numbers to row numbers form another sheet for easy copy and paste:

=INDEX(
  INDIRECT(
    CONCATENATE("QUOTES!",
      REGEXEXTRACT(ADDRESS(ROW(), ((ROW()-3)*5)+4), "[A-Z]+"),
      "4:", 
      REGEXEXTRACT(ADDRESS(ROW(), ((ROW()-3)*5)+4), "[A-Z]+")
    )
  ), 
  COUNT(
    INDIRECT(
      CONCATENATE("QUOTES!",
        REGEXEXTRACT(ADDRESS(ROW(), ((ROW()-3)*5)+4), "[A-Z]+"),
        "4:", 
        REGEXEXTRACT(ADDRESS(ROW(), ((ROW()-3)*5)+4), "[A-Z]+")
      )
    )
  )
)
1
google-apps-scriptgoogle-sheets
Have you had any update since? And btw, you don't necessarily have to use Apps Script to test. When Apps Script reads #REF erroneously, importrange would too. – Argyll

1 Answers

0
votes

Short answer

To avoid the #REF! error, the formula should not be placed on the second row. It could be placed on row 3 and below rows.

Explanation

ADDRESS(ROW(), ((ROW()-3)*5)+4) on any cell of row 2 returns the following error message:

Function ADDRESS parameter 2 value is -1. Valid values are between 1 and 18278 inclusive.