Google Forms Progress Bar, Skip-Logic, and Google Sheets Backups

Google Forms Progress Bar  - if you are using skip-logic in your Google Form, do not use the progress bar. It shows the incorrect percentage complete of the form.  

Creating Google Forms with Skip-Logic – there is no included functionality on Google Forms to create skip-logic. The following instructions are a work around to include skip-logic in your surveys:

1. Create the question and its answer options. Then click on the ‘sections’ icon to create follow-up questions for specific answers  

2. Add a title to the section and the follow-up questions you need: 

3. Click on the ‘three-dot’ icon on the bottom of the question and then click on the ‘Go to section based on answer’ button 

4. Select the section you would like the responder to continue to once they input their answer. (E.g. if the respondent answers ‘Yes’, you will want to redirect them to Section 5 where they will fill out the demographic information of their children. If they answer ‘No’, you will redirect them to the section that continues with the rest of the survey.  

Managing Google Sheet Backups – there is a Google Script you can use to set up a backup trigger for your Google Sheets on a schedule. We chose a monthly schedule, so now on the first day of each month a backup Google Sheet will be created in the Google Drive. Here are the instructions and the Google Script we used below: 

1. In the Google Drive, open the ‘Backups’ folder, click the ‘New’ button on the top left corner of the page and create a new folder. Name this Folder   
2. Open the new folder you just created. In the URL displayed in the browser’s address bar, you will be able to see the folders ID. The Folder ID is the text that comes after the last slack in the URL which is seen below. Copy this ID because we will use it in a few steps.  
3. Open the Google Sheet you want to backup and select ‘Tools’ > ‘Script editor’  
4. In the script editor copy and paste all the code below into the white space. Make sure it replaces the code that is already included.  

function makeCopy() {  

// generates the timestamp and stores in variable formattedDate as year-month-date hourminute-second  

var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");  

// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate  

var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " Copy " + formattedDate; 

// gets the destination folder by their ID. REPLACE xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx with your folder's ID 

// which you can get by opening the folder in Google Drive and checking the URL in the browser's address bar  

var destination = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");  

// gets the current Google Sheet file  

var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())  

// makes copy of "file" with "name" at the "destination"  

file.makeCopy(name, destination); 

 //This section deletes the copies of forms that appear after we back up their associated spreadsheets.  

//Deletes extra Profile Change form  

var files = DriveApp.getFilesByName("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");  

while (files.hasNext()) {  

var file = files.next(); 

 file.setTrashed(true);  

}  

} 

**Make sure the code looks exactly like it does above. It is important that the code that starts with “//” is brown. If it does not look exactly like the code above it will not run.  

5. Copy the Folder ID from step 2 and paste it in the place of the ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ in the code. Make sure that the quotation marks are kept around the ID.  

6. In the bottom block of the code, replace the ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ with ‘Copy of YourNewFormNameHere’. If your newly created form was called ‘Test Form’ then the x’s are replaced with ‘Copy of Test Form’. Make sure that the quotation marks are kept around the form name. 

7. Add a name to the script  

8. Click the ‘Save’ button 

9. Click the clock icon ‘Trigger’ button 

10. Click the ‘Add Trigger’ button on the bottom right corner of the page  

11. Backups will be run on the first day of every month. To set up the backups, select month time, 1, and a time that is staggered with other backup triggers. Click the ‘Save’ button  

12. A pop-up will require you to sign into your Google account. Choose the Insight Sandwich account 

13. Click on the ‘Advanced’ link 

14. Click on the ‘Go to (Folder name) (unsafe)’ link  

15. Click on the ‘Allow’ button to let the script you created run on the Google Sheet 

16. To test that the backup script runs correctly, click on the play button on the menu bar of the script. If no error messages arise check the destination folder that you created in the Google Drive for your backup and ensure that there is a time-stamped backup sheet in the folder.  

 17. Adding new backup triggers should not require you to edit the code beyond changing the text inside of the quotation marks. If new script development is required you can consult Google’s script editor reference guide by clicking the following link: https://developers.google.com/apps-script/reference/drive/