Once Office Forms became available to use last year, one of the features that really got my attention was its Data Sync functionality. Forms would create an Excel spreadsheet I could download locally to my Surface, and every time I opened the Excel file, the little Data Sync add-in would connect to my Form and refresh my spreadsheet with any new entries. I loved this because it allowed me to design and create Forms I could use continuously throughout the year. I could work with the data I was receiving in my Excel file (queries, tables, functions), and then sit back and let it do it's thing over time. I open the Excel file, Data Sync pulls in new entries, my other spreadsheet tables and functions update themselves with the new data, I hit save, and done. This was really handy when I would create and give a Forms quiz to my students and there were students absent that day (and the day after!). I could work on the spreadsheet that night, connect it to Power Bi, and start doing my analysis of the results knowing that when my absent students take the quiz days later all my files will automatically update and refresh the data after I open them.
 The Data Sync add-in that would pull in new results from an Office Form | Then this went away, and it was a bummer. New Forms I would create weren't syncing with the auto-generated Excel file I downloaded. I'd have to hold out on my Excel analysis fun for a few days so I could get those absent students' entries into the spreadsheet before I downloaded it to my computer. I wasn't too happy with this. |
Well, it's back….kind of.
I'm always messing around with Office 365, and I discovered how to get back the data sync back into my excel files. I'll have to change a little bit of my workflow, but whatever, I got my data sync back. Currently (because hey, this could change) there are two ways to create a Form, and only one of them gives you the data sync-like functionality.
I'm always messing around with Office 365, and I discovered how to get back the data sync back into my excel files. I'll have to change a little bit of my workflow, but whatever, I got my data sync back. Currently (because hey, this could change) there are two ways to create a Form, and only one of them gives you the data sync-like functionality.
Method 1: Create a new Form straight from the "Forms" app in Office 365
This will create a Form, and give you a spreadsheet, but the Data Sync ability that refreshes your data table is not there. If you want a static spreadsheet in the end, this is how you do it.
This will create a Form, and give you a spreadsheet, but the Data Sync ability that refreshes your data table is not there. If you want a static spreadsheet in the end, this is how you do it.
Form created from the "Forms" app in Office 365
Method 2: Create a new Form in Excel Online
Start in Excel Online
Create a new spreadsheet in Excel Online and then click on the little Forms button showing in the Home tab. Create a Form. Note! You can't create a quiz so you miss out on the quiz features like math and feedback. This new Form will also show up with your other Forms so you know where to find it, share it, and look at the results. The difference is now the Excel button looks a little different, and instead of giving you an Excel file to download, it directs you to Excel Online file where you can see your Form results in a table. And (best part here), this new Excel Online file is stored in your OneDrive for Business folder, and therefore can sync locally to your computer. Now you have a Excel file that updates itself automatically as new entries are submitted via the Form.
Form created through Excel Online
There is a caveat though I've learned through some trial and error. If I mess around with the synced Form/Excel File (locally and online) like creating new columns and functions in the data table, this breaks the sync connection. The Excel file will no longer import any new responses from the Form.
So, what do I do then? I still want to mess around with my data. It's actually pretty easy, I just create another "Master" Excel spreadsheet and start querying the data. The data coming in from the Form is sent to the Excel Online file, which is synced to my computer. I just query the data table from that data-syncing Form/Excel file stored on my computer and voilà, I have the same data showing up, and it can be played around with. As new entries are submitted, the data is synced to the data-syncing Excel-File-That-Must-Not-Be-Touched, and when I refresh my query from my "Master" spreadsheet, all the new rows of data appear. Yay!
So yeah, my methods will change for next year, but now I've got my data syncing back :-)
So, what do I do then? I still want to mess around with my data. It's actually pretty easy, I just create another "Master" Excel spreadsheet and start querying the data. The data coming in from the Form is sent to the Excel Online file, which is synced to my computer. I just query the data table from that data-syncing Form/Excel file stored on my computer and voilà, I have the same data showing up, and it can be played around with. As new entries are submitted, the data is synced to the data-syncing Excel-File-That-Must-Not-Be-Touched, and when I refresh my query from my "Master" spreadsheet, all the new rows of data appear. Yay!
So yeah, my methods will change for next year, but now I've got my data syncing back :-)
RSS Feed
