Using a script to only take only take cells with values in google sheets

Question

tl;dr how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?

Background

I have an array in Google Sheets with date-ordered data (note dates are in UK format), where I will routinely add new entries every few weeks.

          A               B
1    Recorded date      Value
2    17/3/2018            120
3    1/9/2018             145
4    31/12/2018           210
5    31/3/2019            273
6    5/6/2019             412

In a separate sheet, I want to be able to list other dates, lookup against those dates, and return either the right value (if it matches one of the recorded dates) OR a linear interpolation between the two closest dates.

For instance:

          A               B      C          D                  E
1    Recorded date      Value           Lookup date         Result
2    17/3/2018            120           1/9/2018              145
3    1/9/2018             145           14/2/2019          241.51
4    31/12/2018           210
5    31/3/2019            273
6    5/6/2019             412

(In reality, the lookup cells are daily rows in a different sheet)

I have achieved the simple mechanics of interpolation by adapting the great answers to the question How to interpolate data in a range in Google Sheets. Indeed, I have put my current code below for reference.

PROBLEM: I want to feed the entire column into the script, not just the rows 2-6

My current problem is that this script only works if the inputted x and y ranges cover the exact range of available data. Therefore if more observations are recorded into columns A and B, I have to change the formula.

So in the example above, I need to use the following formula in cell E2 otherwise it returns an error:

=getvalue(A2:A6,B2:B6,D2)

Ideally, the formula in E2 would be the following, which would allow the E column to always work regardless of the number of observations recorded in A and B:

=getvalue(A:A,B:B,D2)

In short: how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?

Here is my current script:

function getvalue(x, y, lookupdate) {
  if (lookupdate.map) {
    return lookupdate.map(function(v) {
      return getvalue(x, y, v);
    });
  }

  if (lookupdate > Math.max.apply(Math, x) || lookupdate < Math.min.apply(Math, x)) {
    throw "lookupdate can't be interpolated !!";
    return;
  }

  var check = 0, index;
  for(var i = 0, iLen = x.length; i < iLen; i++) {
    if(x[i][0] == lookupdate) {
      return y[i][0];
    } else {      
      if(x[i][0] < lookupdate && ((x[i][0] - check) < (lookupdate - check))) {
        check = x[i][0];
        index = i;
      }
    }
  }

  var xValue, yValue, xDiff, yDiff, xInt;
  yValue = y[index][0];
  xDiff = x[index+1][0] - check;
  yDiff = y[index+1][0] - yValue;
  xInt = lookupdate - check; 

  return (xInt * (yDiff / xDiff)) + yValue;
  }

When I run this script with the x/y inputs as A:A and B:B I get the error "TypeError: Cannot read property "0" from undefined. (line 27)."

And line 27 of my script is:

xDiff = x[index+1][0] - check;

Also, this is my first question – so let me know if I should format/ask it differently!

Answer 1

This could be solved without script:

=ARRAYFORMULA(IFERROR(IFERROR(VLOOKUP(D2:D, A2:B, 2, 0), 
 (VLOOKUP(D2:D, SORT(A2:B, 1, 1), 2, 1)+
  VLOOKUP(D2:D, {QUERY(A2:B, "where A is not null order by A", 0),
                {QUERY(A2:B, "where A is not null order by A offset 1", 0);
  "", ""}}, 4, 1))/2)))

Answer 2

You can use Array.prototype.shift() to remove the first row of the values returned by using A:A as the first argument of your custom function.

Example of use

x is variable name for the first argument of =getvalue(A:A,B:B,D2)

var x1 = x.shift(); // x1 will hold the value of A1, x will hold the values of A2:A

Regarding the error message

TypeError: Cannot read property "0" from undefined. (line 27).

where the referred line 27 is

xDiff = x[index+1][0] - check;

occurs because the value of index is the the same of the length of x and index + 1 is beyond the boundaries of x.

For further explanations about how to handle JavaScript Arrays and multidimentional arrays I suggest you to post a question on https://stackoverflow.com

Answer 3

I ended up solving this with a combination of solutions. I used @Rubén's suggestion of x.shift() to remove the header row.

To solve the other parts of my question, I used x.filter() to only keep cells that were not empty (i.e. != "").

I then added a series of nested IF, IF ELSE, and ELSE statements to do the following:

• if lookup date is before the first known record, return a blank cell
• if lookup date is after the last known record, return the last recorded y value
• if lookup date precisely matches an existing record, return that record (part of initial code in my question)
• if lookup date is within the known range, but doesn't precisely match an existing record, calculate an interpolated y value (part of initial code in my question)

Here is the final (solved) javascript code:

function getvalue(x, y, lookupdate) {

/// GET RID OF EMPTY CELLS IN COLUMN
  var xf = x.filter(function(el) {
    return el != ""; 
  });

  var yf = y.filter(function(el) {
    return el != "";
  });

/// GET RID OF HEADER ROW
  xf.shift()
  yf.shift()

/// SAVE THE FIRST AND LAST VALUES
  var firstx = xf[0][0]
  var firsty = yf[0][0]
  var lastx = xf[xf.length - 1][0]
  var lasty = yf[yf.length - 1][0]

/// FIGURE OUT WHAT TO RETURN
  if (lookupdate < firstx) {
      return "";
  } else if (lookupdate.valueOf() == firstx.valueOf()) {
      return firsty;
  } else if (lookupdate > lastx) {
      return lasty;
  } else {
      var check = 0, index;
      for(var i = 0, iLen = xf.length; i < iLen; i++) {
          if(xf[i][0] == lookupdate) {
              return yf[i][0];
              } else {      
              if(xf[i][0] < lookupdate && ((xf[i][0] - check) < (lookupdate - check))) {
              check = xf[i][0];
              index = i;
               }
           }
       }
       var xValue, yValue, xDiff, yDiff, xInt;
       yValue = yf[index][0];
       xDiff = xf[index+1][0] - check;
       yDiff = yf[index+1][0] - yValue;
       xInt = lookupdate - check; 

       return (xInt * (yDiff / xDiff)) + yValue;
    }
}