Backup Your PC and Work With Your Files Using Google Backup and Sync and Google Drive File Stream

Google Drive is a great tool.  It allows us to collaborate, share, and store our files “in the cloud”.  It has completely changed the way I manage my files.  One of the huge benefits of Google Drive is that you can leverage it to store backups of your important files.  Until recently, that was done with the Google Drive Sync application.  The old Google Drive Sync was a powerful tool, but it had some downsides.

  1. It would pull down local copies of everything in your Google Drive.  While having things in two places is a good strategy, I have hundreds of gigabytes of data that I keep in my Drive that I don’t need on a daily basis.  Keeping a copy of this data on local storage was a waste.  You could specify in the client which folders to sync, but as soon as you stopped syncing all folders, folders you created moving forward required you to remember to go into the client and check a box to sync them.
  2. Folders where important files are commonly located on your computer (like your documents, desktop, and pictures) were not synced by default.  You could get around this by moving the target of these folders to a subfolder in your Google Drive, but this process was not intuitive for the average user and always felt like you were tricking Google Drive to sync your stuff.

Fortunately, these problems have been solved using two new(ish) tools from Google.

  1. Backup and Sync – this product is the replacement for the old Google Drive Sync application discussed above.  It separates the “backup” function from the “sync” function.  What most end users really need is the “backup” function.  Most of us can skip the “sync” function because of….
  2. Google Drive File Stream – this product will map a network drive (just like a file share on a server) that represents to your Google Drive.  This allows you easy access to all your files in Drive, without creating a local copy.  The files are navigated and opened  through your file system (just like your other local files).  For example, let’s say you have a native Photoshop file stored in Google Drive.  You can locate that file through the mapped drive that represents your Google Drive, then open it directly in Photoshop.  The file is streamed for use on your computer.  You make your changes, save the file, and those changes are saved directly to the file in your Drive.  No downloading, no uploading, no syncing.  The interaction with Google Drive is almost completely transparent to the user.  You can even access files stored in your Team Drives this way!

With these two applications, you can easily backup the important files on your computer and access all your important files “in the cloud”.  In the video above, I’ll show you how I set these programs using a PC, but there are also Mac clients available.  I’ve provided links to the installers from the video below.

Note: It is important to note that Google Drive File Stream only works with “G Suite” accounts (Like Google Apps for Education or Google Apps for Work).  Google Drive File Stream does not work with individual Google accounts at the time of this article.


If you have any questions about these products, please leave me a comment below.

Mass Data Entry into Web Forms Using Google Apps Script

Recently I was presented with a scenario where a large amount of data that was stored in a spreadsheet needed to be entered into a system with no mass upload feature.  Since the alternative was having someone wear the paint of their keyboard copying and pasting, I needed to automate it.

Most systems provide a way to do this via the uploading of a CSV file.  Some small niche products either lack this feature or require the user to pay for the provider to add the data on the back end.  This system, which shall remain nameless, had this issue.  Here is the interface I was working with:


I have hundreds of names that need to be entered for each year, going back several years.  There were two problems I needed to solve.

Problem #1 – There are only 10 fields to enter data into.

Problem #1 is really easy to fix using the iMacros for Chrome extension.  iMacros allows you to record and play back interactions with a web browser (like clicking a button several times).  Here are the steps I used to get the number of text boxes I needed.

  1. Install the iMacros for Chrome extension.
  2. Go to the page that contains the web interface you need to interact with.
  3. Click the iMacros extension icon to open the interface.
  4. Click Record then Record Macro.
  5. Click the button in the web interface that you would rather not repetitively press (in my case, the “Add More Name” button).
  6. Click Stop.
  7. The iMacros editor will open.  You should have 3 lines of code.
    1. Line 1 is version information, ignore it.
    2. Line 2 tells the web browser to go to the page you are on.  Since we are going to loop this code, we don’t want it opening the web page every time.  To prevent that, simply delete this line.
    3. Line 3 is what “clicks” the button.
  8. Once you have removed line 2, click the Save & Close button.
  9. With the web interface of the system you are using, select #Current.iim (the default macro you just created) in the left column.
  10. Click Play Macro.  This should perform the click, and the result should be displayed (in my case, 2 input boxes are added).
  11. I want to add 100 more boxes however.  Because each click adds two boxes, I’ll need to run this macro 50 times.  Under “Play macro repeatedly”, change “Max” to “50” and press Play Loop.  The macro should run 50 times, and the desired result should be shown in the web interface.

Here is a short video of this in action:

Problem #2 – Get the information from the spreadsheet into the web form.

Here is what my spreadsheet looks like:


I have all the names I want to enter in column ‘A’ of a Google Sheet – all I need to do is automate their entry into all the fields I generated above.  The way I chose to do this is with a keyboard emulator.  I often use keyboard emulators to perform repetitive tasks – they allow you to write code that emulates the pressing of keys on your keyboard.  One example is a physical keyboard emulator I use (USB Rubber Ducky) to automate the process of enrolling Chromebooks.

In this case, I am going to use a software emulator.  WinKeySim is the emulator I chose for this project, there are several other freeware versions available.

In order for this to work, we need to create the list of commands to put into WinKeySim.  Based on WinKeySim’s syntax, the pseudo-code would look something like this:

1 open the command with ["
2 set x to 1
3 add the name from row x
4 if x != the last row
  wait for 200ms [!200] and send a [tab] to go to the next field
  repeat from 3
5 else this is the last row so close the command with "]

I won’t go over the intricacies Google Apps Script here.  I will tell you it is a very powerful tool, and if you use Google Apps and have a need to automate, it will prove very useful to you.  Based on my spreadsheet above, here is the code I used to build the command I used with WinKeySim.

function myFunction() {
 //get the active spreasheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 //get the sheet named 'Sheet1'
 var sheet = ss.getSheetByName("Sheet1");
 //get the row number of the last row
 var lastRow = sheet.getLastRow();
 //create a new google doc to store the emulator commands in
 var doc = DocumentApp.create('Honor Roll Students');
 var body = doc.getBody();
 var text = body.editAsText();
 //start the document with [" (syntax for WinKeySim)
 //loop through each row of the spreadsheet
 //get the name in column A
 var name = sheet.getRange(i,1).getValue();
 //append the name to the sheet
 //if we are not at the last row, add a 200ms delay and
 //send a tab to change fields (syntax for WinKeySim)
 if(i !== lastRow){
 //if this is the last row, end the docuemnt with "]
 if(i !== lastRow){

When executed on the spreadsheet of names, this will spit out a new Google Doc into my Google Drive containing the emulator command I will use to input the data.  It will look something like this:


I can then paste that command into WinKeySim and the data will be entered automatically.  Check out the video below to see it in action.

If you have any questions about this, please leave a comment below!

Exporting Your Files From Google Drive

drive_export_graphicSummer is almost here, and the seniors are about to walk out the door for the last time. That used to mean throwing both their paper and digital items into their respective recycling bins. Now, with their physical and digital lives becoming more and more intertwined, students want to hang onto data for their digital portfolios or to use as a reference moving forward. Whether you are a senior in high school taking the next step, or a staff member moving to a new organization, the ability to separate your data from your current Google account may be very important to you.

You may have asked yourself “Why don’t I just make a personal Google account (or use my existing one), then share all my files with the new account, and change the ownership?”  Well…

  • If you are using a Google Apps domain account (an account that is part of an organization) and not a personal Google account, you won’t be allowed to change ownership of files to an account outside your organization.
  • Even If your situation does allow a change of ownership solution, your first thought will likely be “I’ll just put all my stuff in one folder then change ownership on that folder.”  Unfortunately ownership changes are not applied to child objects.  Eventually I plan on writing a script that allows the use to propagate ownership changes using setOwner() (or more likely someone will beat me to it (or already has)).
  • It is technically possible to share all your files from your domain account to a personal account and then make copies of all the files, but that only works if your organization allows you to share outside the domain.  If you are able to utilize this method, this is usually a better option than exporting (if you plan to subsequently import back into another Google account that is).

star_smallTip: If you decide to used the share and make copies method, your inclination will be to put everything in one folder and then share the folder with the account you are transferring your files to.  This will work just fine, but it is important to note that when you make copies in any shared folder that you have “Can edit” access to (as you would in this example), the copies will be created within that folder (which is on the Drive of the account you are trying to get the data off of).  You’ll need to drag and drop the items into “My Drive” on the account you want to move the data to.

Depending on your situation, exporting your data may makes the most sense.  Before you go down that path, here is what you need to know:

Exporting Pros:

  • Your Google Documents will be converted to standard formats (Microsoft or OpenDocument) so you’ll have versatility with your data moving forward.
  • There are two ways to export your data, they are both really easy to use, and can be used in combination to best suit your needs.

Exporting Cons:

  • Neither export option supports the download of Google Drive file types in their native form. (hopefully this is coming soon – hear me awkwardly ask for it at #io13)  Features unique to Google Drive such as revision history, comments, and sharing settings aren’t supported in the exported formats, so they will be discarded.
  • You are converting your files.  In my experience, converting files back and forth between Google Docs and Microsoft Office (or OpenDocument) works well.  However, anytime “converting” is part of a process, there is potential for problems.  Most of the problems I’ve seen however tend to be easily fixed layout problems, not problems retaining text or images.

Exporting Directly From Google Drive vs. Exporting With Google Takeout

Both methods produce the similar results.  When complete, you’ll have a compressed (ZIP) file containing the items from your Google Drive.  Google Document types (Documents, Drawings, Forms, Presentations, and Spreadsheets) will be converted to the format you selected, while other file types in your drive (PDF, Photoshop, JPEG, etc.) will be left in their original format.

Exporting Directly From Google Drive

The advantage with this method is that it is much easier to export items under “Shared with me” than with the alternative method. The downside is, that you can’t download more than 2 GB at a time.  There is no way to automatically separate your files into multiple downloads, but it is possible to do so manually.  Check out the following video outlining the process.

Exporting With Google Takeout

Google Takeout is a product made by a group of engineers at Google who call themselves the data liberation front.  What is nice about Takeout is that it exports more than just Drive data; it will give you a single zip file containing all (supported) Google data.  The downside of Takeout, specific to Drive, is that your export won’t include files under “Shared With Me”.  Many users only want a couple of items from shared anyhow; they can always just make copies of those specific items and place them in their Drive before they export.  Check out the following video outlining the process.


If you are not permanently breaking up with Google, the best way to get your data out of Drive is to not get it out at all.  If the task of changing ownership or sharing and making copies of your files is both realistic and possible, you should take that route.  If you need to export your data from Drive, I’d suggest using the Drive interface.  I believe the interface is less intimidating for the average user, and coupled with the ability to easily export “Shared with me”, the advantages outweigh the 2GB export limitation.  If you want more Google data than just what is in your Drive, use Takeout in addition to the Drive interface.s  You can export Drive data using the Drive interface, then use Takeout to export your additional data.  They can be used very effectively in combination.

Bonus Round: I’ve Got My Data Out, Now What?

So now you have one or more zip files containing all your converted and downloaded data.  You have a couple of options, and you don’t have to decide now.  If you aren’t prepared to commit to a new home for your data, just put the files in a safe place like a flash drive or cloud storage service (or both!).

If you are planning to use the data on your personal computer in its current Microsoft or OpenDocument format, extract the data from the zip drive and file it away on your computer.  If you have a personal Google Account, or access to your Google Account at your new school or business, you could upload the data there, and convert your files back to Google Drive formats in the process.

  1. Log into your “new” Google account and go to your Drive.
  2. Check your upload settings.  If you want to convert your data back to Google Document types, make sure your upload settings are set to support that.
  3. Extract the data from your zip file and locate the folder containing the data you want to upload.
  4. Finally, upload the folder containing your data.

I hope you found this tutorial helpful.  Let me know if you have any questions or comments below.

Adding Images to Google Forms

Embedding images into forms is a feature that would really enhance the power of Google Forms.  Unfortunately, Google has yet (as of 3/1/2013) to include this feature.  James Eichmiller created a script that allows users to embed images stored in Google Drive into their Google forms very easily however.  Instructions can be found here.

Embedded below is a video made by Bryan Weinert that shows the script in action.  He uses it for inserting exported graphic versions of equations made with Daum Equation Editor.*

* If you teach Math, check out the Daum Equation Editor app available in the Chrome Web Store.