How to Automatically Republish a Google Forms Spreadsheet

August 08, 2014

repub01 You've created a Google Form and you've embedded the Google Spreadsheet of responses into your blog, website, etc. Awesome. The only problem is... it won't automatically republish whenever someone fills out the Google Form. Google was kind enough to place a checkbox for this feature, but only manual updates to the Google Spreadsheet will automatically republish. Unfortunately it currently doesn't republish if your spreadsheet is being updated via Google Forms.

How can we fix this? We are going to create a script that will automatically make a small manual update to the spreadsheet.

First of all, click the File menu, then Publish to the web..., and make sure that you have a check next to Automatically republish when changes are made.

Next, click the Tools menu and then Script editor...

Screen Shot 2014-08-08 at 4.29.33 PM
You may get a pop-up wizard that says "Google Apps Script" and has links to tutorials. You can just close this.

Add the following code:

function ChangeIt() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var t = new Date();
  var x = 'upd: ' + t;
  var range = sheet.getRange('f1');
  range.setValue(x);
}

Where I have sheet.getRange('f1'), you may need to change f1 to the name of some other empty cell.

Save your script and name it something like autorepublish or republishme.

Screen Shot 2014-08-08 at 4.41.41 PM
Click on the little clock icon.

You will get a pop-up that says "No triggers set up. Click here to add one now." Go ahead and click to set up a trigger.

There are two possible ways to set up your trigger...

Run ChangeIt, From spreadsheet, On form submit
Run ChangeIt, Time-driven, Minutes time, Every minute

If you'd like, you can even click Add a new trigger and use both of those triggers.
Screen Shot 2014-08-08 at 4.47.40 PM

Save your current project's triggers. You will next need to authorize the app. The pop-up will say, "This app would like to: View and manage your spreadsheets in Google Drive." Click Accept.

Screen Shot 2014-08-08 at 5.00.05 PM Close your script and go back to your Google Spreadsheet. Click the Tools menu and then Script Manager... You should see ChangeIt listed here. Press the Run button to test it out.

« The Funeral, a comedy...? by Jon Kovach Dodgeball Cat »