# 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? – user0 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. – Tobes 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. – Tobes 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) – Tobes 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. – Tobes 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;
}
}
``````