Google-sheets – Simulate output from a range of values in Google Sheet

You should refer to the sheet name only once, so:

=SUM(Sheet1!B1:B5)

returns the sum of the range B1:B5 in Sheet1.

I wasn't quite sure what you meant by Range as an input to your custom function. Is this the source range or destination range?

Anyways, this is something that should hopefully get you started. There is no timer element, nor is it a custom function. But can be converted to one easily.

The manual timer element can be achieved by adding a trigger. In script editor, go to resources, current project triggers. You can attach a script to a date trigger of your choosing.

function backup(){
/* Edit the vars below this line for your needs */
var sourceSheet  = "Sheet2" ;  // Enter the name of the sheet with the source data
var sourceRange = "A1:B20" ; // Enter the range of the cells with the source data
var targetSheet = "Sheet3" ; // Enter the name of the target sheet  
var targetRange = "A2:B21" ; // Enter the range of cells you wish to copy data to. Note this must be same size as source range.
/* No need to edit below this point */  


var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sourceSheet);
var values = sheet.getRange(sourceRange).getValues();
ss.getSheetByName(targetSheet).getRange(targetRange).setValues(values);

}