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

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] == lookupdate) {
return y[i];
} else {
if(x[i] < lookupdate && ((x[i] - check) < (lookupdate - check))) {
check = x[i];
index = i;
}
}
}

var xValue, yValue, xDiff, yDiff, xInt;
yValue = y[index];
xDiff = x[index+1] - check;
yDiff = y[index+1] - 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] - check;

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

• shouldnt be E2 in your example 145? Jun 27 '19 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 '19 at 20:17
• Hi @user0 yep, you are correct! My error in writing out the example, I have edited it. Jun 27 '19 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 '19 at 15:54

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))) • 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. Jun 27 '19 at 21:37

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

• 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) Jun 27 '19 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 '19 at 21:38
• Thanks, I have added this information to the question. Jun 28 '19 at 9:46

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
var firsty = yf
var lastx = xf[xf.length - 1]
var lasty = yf[yf.length - 1]

/// 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] == lookupdate) {
return yf[i];
} else {
if(xf[i] < lookupdate && ((xf[i] - check) < (lookupdate - check))) {
check = xf[i];
index = i;
}
}
}
var xValue, yValue, xDiff, yDiff, xInt;
yValue = yf[index];
xDiff = xf[index+1] - check;
yDiff = yf[index+1] - yValue;
xInt = lookupdate - check;

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