One Day at a Time

Office Forms: To Sync or not to Sync (the data)

7/13/2017

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.

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.

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 :-)

4 Comments

Rebecca

1/18/2018 06:07:09 am

Andrew -- thank you for this post...I have a Form that suddenly stopped syncing (I have been through many trials and errors and learned not to mess with the raw data table as well!).

When you say you create another "Master" excel file and then query the synced data from there --- did you also have to recreate your Form or were you somehow able to link back to and sync with the original?

Cheers,
Rebecca

Andrew link

1/18/2018 11:47:26 pm

Hi Rebecca,

Yeah. I've basically created brand new Forms his year so my syncing doesn't break. The good news is that I like this new method of syncing, it makes sense. I make sure my Forms are created in the OneDrive Folder I need them in, and then I just leave them alone. If I want to play around with the data, I query those Form/Excel files, pulling their data into one Master Excel file. Here is my process.

When I create my Forms now, I create them from OneDrive ("New" Button --> "Forms for Excel") or straight from Excel Online so I have an Excel file synced locally to my Surface (via OneDrive) that always updates automatically when a new Form submission is entered. This way, my syncing never stops. Also, I don't mess around with these Form/Excel files unless I need to manually change the data in a cell, like fixing a typo.

Afterwards, I create a "Master" Excel file locally on my computer that is also saved in my OneDrive. In that Master spreadsheet, I query ("Get Data" button) those other Form/Excel files and pull in the table data from them. Now I can mess around with the queried data without affecting the original stuff. Every time I open my master file, I just click the "refresh" button in the Data tab, and it pulls in any new data from recent submissions to those Form/Excel files. Everything updates.

If you have data from an older form (that doesn't sync) but you still want to use that data along with data from a new Form (that does sync), you can append them together. If you have a "Master" spreadsheet you're using, I would keep using that one.

1) In your Master spreadsheet, query both the old data table (non-syncing Form) and the data from the newer, syncing Form.

2) In the Master, go to the Data tab, click "Get Data" , choose "Combine Queries", and select "Append".

3) A window pops up that asks you which data tables to combine. Chose the old data and new data table. I would choose the newer one as the Primary Table. After that, an editor pops up. Check to make sure the Append looks the way you want it to. If some of your columns aren't combining correctly, make sure they have the same headers. Then click "Close and Load"

4) Boom. New table with both sets combined. As new Form submissions come in, this new table will update after you click the Refresh button.

Hope this helps :-)

1/19/2018 08:34:54 am

Awesome. Thanks! I have done something similar -- I keep the master data set on a hidden tab and then format and manipulate the responses through cell look-ups on a couple other tabs. Not perfect, but good enough! And it looks like magic to the uninformed...

Discovered that my syncing issue in this instance resulted from a responder putting WAY too many words/characters in one of the comment boxes on my Form. I resubmitted her response without the lengthy narrative and it synced immediately.

Appreciate the help! Was so happy to find your blog post. Sounds like we've been through similar trial and error with these Forms.

Cheers -- and happy music making from a fellow arts nerd!
Rebecca

Jon Goodall

12/13/2022 06:23:07 am

Hi Andrew and Rebecca, I am encountering exactly the same issue, and followed your steps, creating a separate excel master doc, and then using 'get data' function to get data from the excel forms raw data sheet. This works, but as soon as I start tinkering with the 'master sheet, for example, adding formulas in adjacent rows (i.e. to calculate date dif between 2 dates in the survey), the master sheet no longer updates when I hit the refresh button. I thought having the master sheet would enable me to tinker and add formulas etc.? Can either of you help please - spent so much time trying to resolve myself!

Thanks,

Jon

Office Forms: To Sync or not to Sync (the data)

Leave a Reply.

Categories