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

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-custom-functionjavascript

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!

Best Answer

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

0