This should be possible using Google Script. There are some scripts in the Script Gallery that you might find interesting, like EventManagerV3 - Script - Import your events into Calendar in one click. This script relies on the Google Calendar APIs.
Although you will probably have to modify it to suit your needs, it could be a good starting point.
You need to define when data is to be pushed from your spreadsheet to your calendar. Will it be triggered on every change to the spreadsheet? Or is it enough to push data once a day?
You will also have to deal with duplicates - you don't want to create a new calendar event every time a row in your spreadsheet is updated.
To install the script, go to your Google Spreadsheet and click Tools -> Script gallery.
Another possible way is to write your own script, and publish it as a service, in effect creating a web endpoint to your spreadsheet. If you can get it to serve iCal formatted data (or another format that the Calendar software can use), you might be able to set up a subscription from your Calendar system to the spreadsheet endpoint.
I created a small example spreadsheet, and created a script that I published as a web service:
function doGet() {
var result = "";
var ss = SpreadsheetApp.openById("0Avea1NXBTibYdGYzdlluUDV2R3FSTnBPU2JVNmFqeWc");
SpreadsheetApp.setActiveSpreadsheet(ss);
var values = ss.getSheetByName("Ark 1").getDataRange().getValues();
for (var i = 1; i < values.length; i++) {
result += values[i][0] + ": " + values[i][3] + "\n";
}
return ContentService.createTextOutput(result);
}
When invoking the web service, I get a list of events in plain text format.
Now, obviously, no calendar software will be able to parse that format, but it seems possible to create a proper iCal format by modifying the script.
NO - copying google spreadsheet is severly limited and will not copy over YOUR range permission settings but maddeningly then gives everyone edit access - it should at least default to comment access only - its VERY frustrating. So in short the ''copy'' is not a copy.
If anyone discovers that this answer is wrong - PLEASE TELL ME !
Best Answer
As you're aware, Google won't do the job without an internet connection. Even the offline version of Google Docs only allows local changes, not synced ones.
Perhaps surprisingly, Microsoft's Excel does allow simultaneous editing by a number of people on a local network. It isn't real time like Google, but it does update everytime users save. There are simple instructions on this blog post, the relevant part being:
While far from perfect, if everyone is encouraged to save regularly it might be an alternative for your situation.