Slice array in Google Sheets
Many functions in Google Sheets return an array as the result. However, I find that there is a lack of built-in support functions in Google Sheets when working with an array. For example, the GOOGLEFINANCE function can return the historical prices of a stock as a table of two columns and the first-row being headers Date and Close. How can I ignore the headers or remove the headers from the results?
Table of Contents
If my work has been helpful to you 🙏
Make any JavaScript method available in Google Sheets
In JavaScript, there is the SLICE method that can return a part of an array. If I have an array const pricesWithHeader = ['Close', 10.5, 10.3, 10.1, 10.0];, to get only the last 4 elements [10.5, 10.3, 10.1, 10.0], I can apply the SLICE method like const pricesWithoutHeader = pricesWithHeader.slice(1);. How to slice an array in Google Sheets?
Google Sheets has scripting capability with Apps Script based on JavaScript. So to slice an array in Google Sheets, I need to create a SLICE method in Apps Script that wraps the original SLICE method of JavaScript. By doing so, the SLICE function is available to use in every cell of a spreadsheet.
Demo and source code
Demo spreadsheet: How to slice array in Google Sheets
References
Disclaimer
The post is only for informational purposes and not for trading purposes or financial advice.
Here's a Google Sheets formula similar to JavaScript slice() to return a subset of rows from a range/array:
ReplyDelete=let(
array,A74:A81,
start,B72 +n("First row is 1, last row is -1"),
end,B73 +n("Same row counting"),
startv, if(start=0,1,start) +n("0 starts from the first row"),
endv, if(end=0,-1,end) +n("0 ends at the last row"),
rows,rows(array),
offset,max(0,if(startv>=0,startv-1,rows+startv)),
limit,max(0,if(endv>=0,endv-startv+1,rows+endv+1-offset)),
q, query(array,"select * limit "&limit&" offset "&offset&" "),
q
)
It can be converted to a named function, then used like: =SLICE({1,2,3,4,5}, 2,-2)
ReplyDelete