Google-sheets – Use Google Forms to allow updating of old data and linking to correct record

The spreadsheet is not the actual repository of responses. There is a 'bucket' of responses that is connected to the form itself.

The data presentation features use the responses connected to the form.

You need to use a script to add the extra responses to the Form object. You won't be able to save the original dates with the old responses, as the timestamp is always when you add a response to the Form.

The process works like this:

   +-------------+
   |Form         |----+
   |-------------|    |
   |Responses    |    |
   |             |    v
   |             |  User
   +-------------+  submits
         ^          form
         |            +
     Saved to         |
         |            |
         |            |
   +--------------+   |
   |   Response   |< -+
   +--------------+
         |
         |
      Copied to
         |
         v
   +-------------+
   |Spreadsheet  |
   |-------------|
   |             |
   |             |
   +-------------+

When the response is saved, it is saved to the form and copied to the spreadsheet.

From a different answer (relevant code attached) - you can use something like the test submission technique I used to populate the form with the responses. You would have to get your answers from the spreadsheet, and use the appropriate item type.

function testOnSubmit() {
  var answers = [
    ["Sue", "39", "Okay I suppose"],
    ["John", "22", "Great"],
    ["Jane", "45", "yeah no"],
    ["Bob", "33", "Super"]
  ];

  var form = getConnectedForm();
  var items = form.getItems();
  for (var i = 0; i < answers.length; i++) {
    var formResponse = form.createResponse();
    for (var j = 0; j < items.length; j++) {
      var item = items[j];
      var itemResponse = item.asTextItem().createResponse(answers[i][j]);
      formResponse.withItemResponse(itemResponse);
    }
    formResponse.submit();
    Utilities.sleep(500);
  }

}

With the following Google Apps Script you can grab the LIBOR value and added it to a new sheet. Goto the script editor:

and add this script:

UPDATE 25-01-2013
I've noticed that the trigger occasionally troughs a NA#. Most probably because the GoogleFinance formula takes some time to get its info. Therefore I revised the snippet a bit, to have a sleep function added to it:

NEW

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // sleep
  Utilities.sleep(6000);

  // get cell value
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

OLD

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // flush the spreadsheet before using it  
  SpreadsheetApp.flush();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get cell value from sheet[0]
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}  

The clue to the story is to create a time based trigger:

and set the trigger to execute at 5-6 pm:

See the example file I´ve created: LIBOR. I've added an extra menu item, so that you can execute the script this way.