Automatically Copy Data Between Google Sheets

Question

I am looking for some help automating copying data between google sheets.

I have a sheet called "Current Data". This sheet has data in columns A-F(with headers). Column A contains usernames. Columns B-F will have formulas which pulls data from Instagram.com using

=VALUE(REGEXEXTRACT(IMPORTXML(

I got the formulas from this website: https://www.benlcollins.com/spreadsheets/import-social-media-statistics/

There is another sheet called "Historical Data". This sheet contains the same columns as "Current Data" (A-F, with headers). This sheet contains all data from the "Current Data" sheet, pasted daily.

My Current Process:

Open Sheet Navigate to "Current Data" sheet, copy values from A2-FXXX
Navigate to "Historical Data" sheet, scroll to next blank row, paste data.

I am looking to automate this and have it occur daily.

I am using this script to automatically update my IMPORTXML function. This works great. Periodically refresh IMPORTXML() spreadsheet function

I am copying the values from the Current Data sheet to the Historical Data sheet, using this script. copy and paste with google spreadsheet script.
- This script also works, but it is only copying the first line of data to the destination. The script is also wiping the data from the Current Data sheet.
- I removed the "source.clear ();" from the code, but the data still gets wiped.

I also tried using this script, as some people mentioned users needed to use appendRow instead of copyTo. Still no luck with this code:

function moveValuesOnlyy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange("CurrentData!A2:F100");
  var destSheet = ss.getSheetByName("HistoricalData");
  destSheet.appendRow(source.getValues());
 }

In the end I am looking for the script to:

Find and copy the data range from the "Current Data" sheet, starting with A2.
Find next empty row on "Historical Data" sheet.
Paste as values.

Any help would be greatly appreciated, thank you!

Update your post with your exact script that is only giving you the first line (with your document names and things). — J. G.

Cooper Cooper · Accepted Answer · 2019-03-20T23:36:47

Try this:

function copyPaste() {
  var ss=SpreadsheetApp.getActive();
  var srcsh=ss.getSheetByName('CurrentData');
  var dessh=ss.getSheetByName('HistoricalData');
  var srcrg=srcsh.getRange('A2:F100');
  var data=srcrg.getValues();
  var desrg=dessh.getRange(dessh.getLastRow() + 1,1,99,6);
  desrg.setValues(data);
}

Automatically Copy Data Between Google Sheets

1 Answers