To use Google Sheets, first upload the data. You will probably want to start a new Spreadsheet for this importing process, rather than opening an existing one, although at the import stage you get the choice to replace the current contents, add a new sheet to the existing spreadsheet, or create a new spreadsheet document.
Sign in to your Google account and then visit Sheets and click "create new Spreadsheet", or visit here:
https://docs.google.com/spreadsheets/create
Once the spreadsheet loads, bring up the File Menu, then select the Import option.
At this point you will need to have your data ready in a suitable format for import, ideally in .csv or .xlsx format.
If not then use the Guides "Table of Contents" to jump back a page in this guide and convert your data file before continuing.
Selecting the Upload option will allow you to drag-and-drop files or select them using the File Open dialog.
Alternatively you may have your files already in My Drive or via Shared with Me.
You'll be presented with the option to Import the file you've selected / uploaded. It will automatically detect the separator as comma.
You may wish to alter the Import Location, so that you can select an alternative destination from this list:
- Create new spreadsheet
- Insert new sheet(s)
- Replace spreadsheet
- Append to current sheet
- Replace data at selected cell
Leave the checkbox checked to allow automatically converting text to numbers, however if it causes problems then untick that option and try again.
You will be presented with your new data, rejoice!
Now it's time to do a bit of simple time conversion and then some sanity checking.
Start with adding a column between timestamp and value columns, by right clicking on the value column header (column C in screenshots) and choosing Insert 1 column to the left.
You can find the same option under the Insert menu on the Menu toolbar, then the Columns sub menu.
Add a column title like DateTime, and then move down one cell, out of the header row, to the first data row in that column.
This is where a formula can be used to convert the timestamps from UNIX Epoch time to a normal spreadsheet Date Time value.
You can enter the formula in the cell body, or use the formula bar above the spreadsheet to more easily see long entries.
In the first empty cell in the new column, enter the equals symbol (=) to begin a formula, then the word EPOCH and accept the suggestion of EPOCHTODATE.
Then your cursor will be positioned inside the brackets of the EPOCHTODATE function in the formula bar.
Enter the address of the timestamp cell to your left, e.g. B2, or click on it to automatically populate the cell address into the formula.
Hitting enter or return will complete the formula and move the focus to the next cell. You will want to then reselect the updated cell showing the converted date time value.
You will usually find the software suggests filling in the same formula for the remainder of the columns empty cells, accept this if offered.
Alternatively you can double click on the cell marker, positioned in the bottom right corner of the cell, to fill the cells beneath automatically up to the length of cells in the adjacent column next to it. Usually completing the formula with Ctrl + Enter will also fill the range of cells with the formula.
You can easily confirm that the cells were filled to the end by using the Ctrl key combined with down or up arrow keys to navigate to the end or beginning of the current block of data (but it doesn't jump over gaps in the data).
Lastly I added an additional column for Component Name, and set its formula to use the CONCATENATE function, with the i2c_address cell and the pin cell as the arguments (you can add more if you wish).
It might make sense for you to also include the si_unit, and I2C multiplexer information if present.
My formula for the first data row in the Component Name column was =CONCATENATE(A2, F2) and then that was used to populate the entire column, giving me one column to distinguish between sensors.
So you've got your lovely data, but after a quick scroll through, you've spotted some problematic values, maybe some NAN's (Not A Number), zeros, or minus ones (only you can know what abnormal means for your experimental data).
Don't panic, it's simple to filter data and temporarily remove (filter out) the unwanted values.
Filtering can also be used to simply split a dataset that may be composed of multiple types of data (like ours has humidity and temperature which won't graph very well together).
Let's setup a filter for our data to only include the values with the units matching 'C' for Celsius, and to only include the I2C address for one sensor, the SHT30 at I2C address 0x44, so we want to filter out blanks and 0x18, as that was the main board enclosure internal temperature sensor (MCP9808).
Usually it's enough to have the focus on a cell anywhere in the dataset and choose to add a filter, but for superstitious reasons I prefer to be in the header row and ideally on the column with unbroken data (like the timestamp column) to ensure the software grabs all the data rows correctly. You can also pre-select the data first before creating a filter (holding Ctrl key, or Apple key, and pressing A).
Goto the Data menu in the menu toolbar, and then select the option to Create a filter. The same option is available through the right click / context menu.
Now each column has a green filtering symbol next to the title, it looks a bit like an upside-down triangle made of the base line, middle line, and a dot.
Here's where you can remove (filter out) based on certain conditions, or directly (de)select values.
Clicking the green arrow / triangle will bring up the filter drop-down menu for that column.
Once a column has a filter applied then the green triangle becomes a green funnel showing data is being filtered in that column.
Here the i2c_address column has a filter option showing the 3 possible values, blank (when a component uses 'pin' instead), or 0x18 for the MCP9808, and 0x44 for the SHT30. Untick the 0x18 value and untick blanks too.
Do the same to filter the si_unit column to be 'C' only (for temperature in degrees Celsius).
Charting a path to victory, or how to graph the right things
Now select all the data or move to the first cell in the header row, and choose Insert Chart from the Insert menu (or right click / context menu).
It's not ready yet, but after changing a few things like the horizontal X axis to be the Date Time column, and only the values column for the Data Series, things start to come together.
Start with selecting the chart style / type, a line chart is often a good starting choice. Then confirm the data range is correct, you may wish to remove the value and then use the 4 boxes icon to bring up the suggestions, as the first suggestion in that list is usually the right one. You can also use a comma separated list of ranges.
Doing the same with the X axis (removing suggestion) and using the Add X-axis button brings up the column names, making it easier to pick the DateTime column.
Now add the values. Choosing Add Series and selecting the value column should produce a recognisable graph. The final checkboxes are your choice, I like to Use row 1 as headers, but be sure to have the final checkbox unchecked, captioned Treat labels as text or the dates will be incorrectly handled.
You may wish to set up the tick marks and min/maximum values for each axis, this is available on the second tab labelled Customise.
To get back to this setup, use the triple dots menu in the top right of the chart area. It also allows downloading and moving the chart to its own worksheet.
If you change the filter, then the graph updates, but you'll notice it doesn't display well with more than one sensor. This is because it wants a separate column for each data series. We'll cover that in a minute, but to make life easy and get individual graphs for each sensor, you can create a new sheet and use a formula to include only filtered data from Sheet1 (or whatever the worksheet containing the imported data is named).
Filter formula for a new sheet of just one sensor
After inserting a new worksheet using the Plus icon in the bottom left corner, or from the menu toolbar, go to the second cell, A2, and enter the beginning of the formula =FILTER(. The function takes the data range as the first argument, and then filter criteria for any remaining arguments.
So we want the data worksheet name in single quotes ( ' ) followed by an Exclamation mark ( ! ) and then the columns to include ( A:F ) for the first argument. Then a comma to separate the first and second arguments.
Then the filtering criteria, again the worksheet name in quotes followed by exclamation mark and then the first cell or whole column for i2c_address ( A:A ), and then equals and then the value 0x18 in double quotation marks ( " ) and then if we wanted to filter the si_unit too, we would do another criteria but for column E. Finally, a closing bracket finishes the function. It should look similar to this:
=filter('konbert-output-6fcac085'!A:J,'konbert-output-6fcac085'!A:A="0x18")
Now you'll notice after hitting enter it takes a moment, then all the data from the other sheet appears, but filtered by the criteria in your formula. It has its own filtering / grouping options without affecting the original table of data. Unfortunately it doesn't copy the header row, so you'll want to copy that from the original data source worksheet.
Doing this for each sensor might be wise, but there's also another option...
It's a horizontal world - using horizontal data for combined charts
By adding a new column for each component to the original table, it's pretty easy to have a formula that only prints the value column data ( E ) if the component name ( A ) matches and otherwise an empty string ( "" ).
=if($A2="0x44",$E2,"")
Here the dollar ( $ ) means don't change the column as you move/copy this formula, so $A2 will still refers to the I2C address, and $E2 will point at the value column, when the formula is used to fill in both the 0x18 and 0x44 columns (obviously adjusting the check for ="0x44" ).
That then gives you the value field copied to the new sensor column for I2C address 0x44 (SHT30). Do similar for the other components, but update the column for pin components if needed. You can now graph a combined dataset by selecting each Series as one of the new columns, instead of using the values column.
It's worth mentioning at this point you can convert the data into a table, which gives the ability to save views (filters), along with grouping the data by columns. Most helpfully, you can also use table names as references in the formula bar.
Note that a table also easily converts to a pivot table, which is used to aggregate data, e.g. average/max/min temp grouped by (per) hour. You can find Pivot table under the Insert menu. Then from your pivot table use the right click / context menu in the date column to choose Create pivot date group.
Lastly don't be afraid to use the A.I. assistant built into Google workspaces (or your PC/browser), they all understand spreadsheet formulas and can offer simple solutions that otherwise might take you a while to find.
Got bigger ideas for your data? No problem, it's easy to use it inside a data analysis notebook, which we'll show on the next page, or via the Google colab platform (with generous free compute allowance), they even have a handy example notebook, and browser extension, or take your newly organised data off to another site entirely.
Page last edited April 02, 2025
Text editor powered by tinymce.
