There may be a more eloquent way, but this is the way I came up with: The function to find the last populated cell in a column is: =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ) So if you combine … Read more
When a cell has only one URL, you can retrieve the URL from the cell using the following simple script. var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”); var url = sheet.getRange(“A2”).getRichTextValue().getLinkUrl(); //removed empty parentheses after getRange in line 2 Source: https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6
Answer: IMPORTXML can not retrieve data which is populated by a script, and so using this formula to retrieve data from this table is not possible to do. More Information: As you’ve already mentioned, you can attempt to get the data directly from the table using: =IMPORTXML(“https://kamadan.gwtoolbox.com/”,”//table[@id=’trader-overlay-items’]”) Which just gets a blank cell. I went … Read more
Custom functions will do nothing but return a value to a cell in which they reside unless the return an array which may affect contiguous cells. A custom function cannot initiate a message box(Browser.msgbox()) A custom function is a function entered as all or part of a formula in a spreadsheet cell that calls a … Read more
Note: when it says “B5” in the explanation below, it actually means “B{current_row}”, so for C5 it’s B5, for C6 it’s B6 and so on. Unless you specify $B$5 – then you refer to one specific cell. This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas In your case, you will need to set … Read more
QUERY level 1: if all 5 cells in range C2:G have values: =QUERY(QUERY(C2:G, “select (C+D+E+F+G)/5”), “offset 1”, ) if not, then rows are skipped: if empty cells are considered as zeros: =INDEX(QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )) to remove zero values we use IFERROR(1/(1/…)) wrapping: =INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, “select (Col1+Col2+Col3+Col4+Col5)/5”), “offset 1”, )))) to make Col references … Read more
No, there’s no equivalent to Excel’s EVALUATE() in Google Sheets. There’s long history behind this one, see this old post for instance. If you’re just interested in simple math (as shown in your question), that can be done easily with a custom function. function doMath( formula ) { // Strip leading “=” if there if … Read more