Google-sheets – Color Shading with Google Spreadsheets

No, Conditional formatting in Google Spreadsheets can only edit text and cell background color. You cannot use it to edit cell values. As Brian P pointed out you can use scripts to do it.

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.