Sheet ReserveSpreadsheets worth keeping.

Formats

Can Google Sheets open ODS files?

Short answer

Yes. Google Sheets imports OpenDocument Spreadsheet files. Choose File > Import > Upload, select the ODS file and pick an import location; the data becomes a regular Google Sheets file. You can also export back with File > Download > OpenDocument format (.ods). Formulas and basic formatting carry over, but macros and some advanced features do not.

Updated

Google Sheets supports ODS in both directions. It can import .ods files, and it can download any sheet in ODS format. The same import list includes .xlsx, .xls, .csv, .tsv and .txt.

How do I import an ODS file into Google Sheets?

  1. Open Google Sheets and create a blank spreadsheet, or open the one you want to add the data to.
  2. Choose File > Import.
  3. Open the Upload tab and drag in the .ods file, or choose Browse. If the file is already in Google Drive, use the My Drive tab.
  4. Choose an import location: Create new spreadsheet, Insert new sheet(s), or Replace spreadsheet. For spreadsheet files, options that only make sense for plain text, such as appending rows, are not offered.
  5. Click Import data.

The result is a normal Google Sheets file. The original ODS file is not changed.

How do I export a Google Sheet as an ODS file?

Open the Google Sheet and choose File > Download > OpenDocument format (.ods). The download contains all sheets in the spreadsheet.

What carries over when I open an ODS file in Google Sheets?

Usually preserved:

  • Cell values and text.
  • Standard formulas such as SUM, IF, VLOOKUP and PMT.
  • Number and date formats, fonts, fills, borders and column widths.
  • Multiple sheets, merged cells and frozen rows.

Often changed or dropped:

  • Macros. LibreOffice macros do not run in Google Sheets, and Apps Script is not exported to ODS.
  • Some chart types and chart formatting.
  • Pivot tables, which may be rebuilt differently or imported as static values.
  • Conditional-formatting rules and data validation that Sheets does not support.
  • Functions that exist in one program but not the other.

What happens to a PMT formula when an ODS file opens in Sheets?

You receive loan.ods from a colleague who uses LibreOffice. It has a payment cell with =PMT(B3/12,B4*12,-B2). After import, the formula appears unchanged in Sheets and returns the same value, because both programs use the same PMT syntax. If the file also used a LibreOffice-only function, the cell would show #NAME? and you would need to rewrite it. Check a few key totals after importing.

Is there a cell limit when importing an ODS file to Google Sheets?

Google Sheets caps a spreadsheet at 10 million cells across all sheets, and very large uploads may fail or time out. If an ODS file is large, check how many cells it holds before importing.

What can go wrong when an ODS file moves to Google Sheets?

  • Sheets recalculates everything on import. Small differences in how edge cases are handled, such as empty-string cells or very old dates, can show up.
  • Fonts missing from Google's library are substituted.
  • Saving an edited Sheets file back to ODS creates a new file. Edits do not sync to the original.

For a comparison of the formats, read what an ODS file is or the post on which spreadsheet format to use.