script to force republish of google spreadsheet

Question

I have created a form that pushes data to a Google Spreadsheet. The data is latitude, longitude, location, and other identifying data. The spreadsheet is then published as a .CSV file and imported into ARC GIS to be displayed on an interactive map. It works exactly as I wanted and I set it to republish after each change.

The problem is that when the spreadsheet has rows appended by the script, it is not seeing it as a change and republishing. In order to get the updated data imported to the map, I need to go in and manually republish. Is there anyway through the Google Apps Script that I could make a few lines of code to force a republish? I could then add that to the "on form submit" script I have or another time based one that already runs at 3 am everyday.

I have looked through the Google Apps Script documents and not found anything. When searching for help on the web, the overwhelming majority of responses are for how to publish your script as a template for other.

no. When a form is submitted, that modifies it by adding a row of data. At 3 am it does other functions where it reads values and copies them to another spreadsheet. The last time I manually republished it was 2/22 and since then around 20 times a row was added. It still has a last published date of 2/22 and the map still does not have the new values added. — user1791058
Do you upload the CSV manually to ARC GIS or do you just provide it with a URL and it goes and fetches the data? — Arun Nagarajan
I provide the url for the csv version to ARC GIS. It displays properly. Once I manually republish it shows the correct GIS data. It just seems as though changes made to a spreadsheet through script are not recognized as changes for purposes of determining if it should republish. If you manually type in new data, that is seen as a change and causes a republish. Does anyone know of a way to use the google apps script to force a republish? — user1791058

Steve Whisenhant Steve Whisenhant · Accepted Answer · 2013-03-17T01:01:31

My testing sheet was republished after the following function was executed by either a menu entry or a time-based trigger.

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

If I were in your shoes, I'd add an extra column to the end of the sheet with some benign constant data that a script can change without affecting the systems consuming the data. If an extra column isn't an option, try modifying my sample to read in a current value, change it, and immediately change it back.

Also, I'd see if the spreadsheet onEdit() trigger fires when the form submit adds a new row. If so, tie your GAS function to it to force the republish. If not, setup a timed trigger to execute the GAS function.

script to force republish of google spreadsheet

2 Answers