Here is a sample script that uses a service account to read the contents of a Google Spreadsheet’s sheet. Have a look at the README for instructions to set it up: https://github.com/juampynr/google-spreadsheet-reader
Use MATCH to determine whether each row in column A appears in column B, then filter column A to only the rows for which MATCH returned #N/A (i.e., that row’s value in column A could not be found in column B): =FILTER(A:A, ISNA(MATCH(A:A, B:B, 0))) If A contains duplicates and you want to reduce the … Read more
If you want something that just works in Google Spreadsheets (as the title suggests), you can use open-ended ranges: =SUM(A2:A) In Excel, you can specify the maximum rows for that version; for example, for 2007 and 2010: =SUM(A2:A1048576) This will work in Google Spreadsheets as well, and is beyond the current theoretical row limit in … Read more
The new Google spreadsheets use a different URL (just copy your <KEY>): New sheet : https://docs.google.com/spreadsheets/d/<KEY>/pubhtml CSV file : https://docs.google.com/spreadsheets/d/<KEY>/export?gid=<GUID>&format=csv The GUID of your spreadsheet relates to the tab number. /!\ You have to share your document using the Anyone with the link setting.
I had the same question this year (2021), and it looks like there’s support for it now. In short, you just have to double-click on the empty legend item for the series, and it’ll give you a place to put text, directly in the chart. Here’s what my chart looked like (with no series titles): … Read more
The debugger is probably not lying to you – if you launch that function in the debugger, it will have no parameters passed to it. No worries, though, you just need to make sure that you get values to use for debugging. Take a look at How can I test a trigger function in GAS?, … Read more
This is what pivot tables are for. I have created an example using your data. First, add a column to format the date as your monthly string, eg “2009-01” with the formula: TEXT(A2, “YYYY-MM”) Then highlight the data, and choose “data” > “pivot table report…” For Rows, select “month” For Values, select “amount” TADA! That’s … Read more
I have exactly the same issue, and it seems that so far (March 2018) Sheets v4 API does not allow to search by value, returning cell address. The solution I found somewhere here on StackOverflow is to use a formula. The formula can be created in an arbitrary sheet each time you want to find … Read more
As of now (June, 2020), it is possible to do what you described. Here is an official documentation. Shortly: Select part of text where you want to add a link, then click Insert -> Link or using shortcuts – CMD + K/CTRL + K. Personally, when I was clicking Insert from menu bar, my text … Read more
Edit: this one is simplier: =REGEXEXTRACT(A1,”[^/]+$”) You could use this formula: =REGEXEXTRACT(A1,”(?:.*/)(.*)$”) And also possible to use it as ArrayFormula: =ARRAYFORMULA(REGEXEXTRACT(A1:A3,”(?:.*/)(.*)$”)) Here’s some more info: the RegExExtract function Some good examples of syntax my personal list of Regex Tricks This formula will do the same: =INDEX(SPLIT(A1,”/”),LEN(A1)-len(SUBSTITUTE(A1,”/”,””))) But it takes A1 three times, which is not … Read more