1

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!

4
  • shouldnt be E2 in your example 145?
    – user0
    Jun 27, 2019 at 19:31
  • Welcome. What is the textual error that you are getting? By the other hand, you custom function use pure JavaScript, so it's very likely that Stack Overflow is a better home for this question.
    – Rubén
    Jun 27, 2019 at 20:17
  • Hi @user0 yep, you are correct! My error in writing out the example, I have edited it.
    – Tobes
    Jun 27, 2019 at 21:18
  • The code isn't complete as the function included is getvalueTEST, it calls getvalue but this function isn't included.
    – Rubén
    Jun 28, 2019 at 15:54

3 Answers 3

0

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

1
  • 1
    Thanks @user0 – that does seem to work. I had also figured out a way to do it with a bunch of VLOOKUPs, but I am trying to script it because in my actual sheet I need to pull multiple of these together in a single cell, and its quite daunting (not to mention high likelihood of error) if I try to string together several of those formulas.
    – Tobes
    Jun 27, 2019 at 21:37
0

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

3
  • Thanks @Rubén that seems to help get rid of the first row, but the script still fails with A:A and B:B, presumably because it is passing an entire column (mostly empty) into the script. I get the error 'TypeError: Cannot read property "0" from undefined. (line 27).' Any idea how to solve that? (Also thanks for the tip about Stack Overflow for pure javascript, good to know)
    – Tobes
    Jun 27, 2019 at 21:34
  • @Tobes Please add the error to the question and highlight / point out someway the line 27 that the error refers to.
    – Rubén
    Jun 27, 2019 at 21:38
  • Thanks, I have added this information to the question.
    – Tobes
    Jun 28, 2019 at 9:46
0

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;
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.