How to negate ‘isblank’ function
I suggest: =not(isblank(A1)) which returns TRUE if A1 is populated and FALSE otherwise. Which compares with: =isblank(A1) which returns TRUE if A1 is empty and otherwise FALSE.
google-sheets
ISO-8601 String to Date in Google Sheets cell
To get an actual Date value which you can format using normal number formatting… =DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8)) eg. A B 1 2016-02-22T05:03:21Z 2/22/16 5:03:21 AM Assumes timestamps are in UTC Ignores milliseconds (though you could add easily enough) The DATEVALUE() function turns a formatted date string into a value, and TIMEVALUE() does the same for … Read more
How do I avoid the “#DIV/0!” error in Google docs spreadsheet?
Wrap your formula with IFERROR. =IFERROR(yourformula)
Accessing Google Spreadsheets with C# using Google Data API
According to the .NET user guide: Download the .NET client library: Add these using statements: using Google.GData.Client; using Google.GData.Extensions; using Google.GData.Spreadsheets; Authenticate: SpreadsheetsService myService = new SpreadsheetsService(“exampleCo-exampleApp-1”); myService.setUserCredentials(“jo@gmail.com”, “mypassword”); Get a list of spreadsheets: SpreadsheetQuery query = new SpreadsheetQuery(); SpreadsheetFeed feed = myService.Query(query); Console.WriteLine(“Your spreadsheets: “); foreach (SpreadsheetEntry entry in feed.Entries) { Console.WriteLine(entry.Title.Text); } Given … Read more
Convert Unix Epoch Time to Date in Google Sheets
The simplest way, not requiring any JS programming, would be through a formula, dividing by 86400 seconds per day and adding to January 1, 1970. For example the following gives 21 July 2017: =1500598288 / 86400 + DATE(1970, 1, 1) To convert a whole column of numbers, just use ARRAYFORMULA: =ARRAYFORMULA(A:A / 86400 + DATE(1970, … Read more
How to union ranges in google spreadsheets
Just use: ={sheet1!a:a; sheet2!a:a}
Selecting the last value of a column
Similar answer to caligari’s answer, but we can tidy it up by just specifying the full column range: =INDEX(G2:G, COUNT(G2:G))
Download link for Google Spreadsheets CSV export – with Multiple Sheets
Every document in Google Sheets supports the “Chart Tools datasource protocol”, which is explained (in a rather haphazard way) in these articles: “Creating a Chart from a Separate Spreadsheet” “Query Language Reference” “Implementing the Chart Tools Datasource Protocol” To download a specific sheet as a CSV file, replace {key} with the document’s ID and {sheet_name} … Read more
Refresh data retrieved by a custom function in Google Sheet
Ok, it seems like my problem was that google behaves in a weird way – it doesn’t re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn’t re-connect to the API and doesn’t re-fetch the price, it simply returns the previous script result … Read more
Convert column index into corresponding column letter
I wrote these a while back for various purposes (will return the double-letter column names for column numbers > 26): function columnToLetter(column) { var temp, letter=””; while (column > 0) { temp = (column – 1) % 26; letter = String.fromCharCode(temp + 65) + letter; column = (column – temp – 1) / 26; } … Read more