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
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.
Feedback
If you have any feedback, question, or request please:
- leave a comment in the comment section
- write me an email to allstacksdeveloper@gmail.com
Support this blog
If you value my work, please support me with as little as a cup of coffee! I appreciate it. Thank you!
Share with your friends
If you read it this far, I hope you have enjoyed the content of this post. If you like it, share it with your friends!
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