Create a function to copy range (values only) to a new tab at specified intervals

Question

Looking to find a function that can copy the values only from a range I am importing from the web at a certain time. Have found a lot of functions that are similar to what I need but can't nail one down.

So basically, I want a snapshot of my data at least once a week on a date/time that I specify. Would I be able to create something like this

=CopyValuesOnly(Range, Date/Time)

I would need to manually specify my date/time as this could vary from week to week. Was thinking of just putting this in a cell in my new tab for the data and the function could call it in rather than having this date/time hard coded within the function.

Any suggestions welcome as I have limited experience within app script.

Answer 1

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);

}