spreadsheet snippets

How to export a Google Doc spreadsheet to CSV

Tagged csv, json, google, spreadsheet  Languages 

It's no longer possible to export Google Spreadsheet's to CSV, so use JSON instead:

https://spreadsheets.google.com/feeds/list/<SPREADSHEET_KEY>/od6/public/values?alt=json

You can find the SPREADSHEET_KEY in the Google Doc's original URL.

You can also use the Google Drive API.

Edit  Delete

Total hours between start and end time - Google Spreadsheet

Tagged format, google, spreadsheet, time, time report  Languages 

I want to extract the total hours worked from a Google spreadsheet.

Given the following data in a Google spreadsheet:

  • End time in column D using time format
  • Start time in column C using time format

For example:

start,end,hours
10:00,12:00,2
9:00,12:00,3

To achieve this in a Google spreadsheet we can use the following formula:

=ARRAYFORMULA((HOUR(D496-C496))+(MINUTE((D496-C496)))/60)

Reference: https://infoinspired.com/google-docs/spreadsheet/payroll-hours-time-calculation-in-google-sheets/

Edit  Delete