Any way to take a value from a cell in another sheet in Google Sheets only once?

Question

I am taking data from Google Analytics into a monthly report in Google Sheets. I have one sheet which pulls in the data which I plan to run at the end of every month. The data I am pulling through is visits and conversions for a number of site URLs. I would like to show month on month data so was planning to have another sheet which takes this data and displays the values per month so it's easy to compare.

My issue is that when I re-run the report it would update each month rather than the latest month.

Is there any way to make a cell in one sheet pull through data from another but not auto-update afterwards?

Answer 1

This requires a script, which can be executed periodically by a trigger, or manually. Here is an example of a script that copies certain data from one sheet to Archive, where it will not be modified (it appends data to the bottom of Archive):

function recordChanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Current');  // source sheet
  var target = ss.getSheetByName('Archive');  // target sheet

Then either

  var range = source.getDataRange();   

to get all data in source sheet; or

  var range = source.getRange(source.getLastRow(), 1, 1, source.getLastColumn()); 

to get the last row of source sheet; or

  var range = source.getRange("A1:D10"); 

to get a specific range.

Finally, copy the values over:

  var values = range.getValues();
  target.getRange(target.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}