Copy Data from Sheet1 and Paste in Sheet 2

Question

I'd like to input a set of numbers on a weekly basis, after that week is up, click a button and it takes the data and moves it to Sheet2. I'd like to "add to" sheet2. I've tried several different things/ways...

Find last Column Used with an import range... That works but it takes awhile to run and my script ends up pasting it 6 times.

Currently what is working is a insert column... But, when I do that it breaks my "summary" tab on Sheet1.


    function lastColumn() 
{

  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var lastrow = sheet2.getLastRow();

  for(var i = 1; i <= lastrow; i++)
  { 
    var column = 1;

    while(sheet2.getRange(i,column).isBlank() == false)
    {     
     column = column + 1;      
    }   
    
    sheet2.getRange(i, column).setFormula('=IMPORTRANGE("https://docs.google.com/spreadsheets","yay")');   
  }

}

This is the one that is sloppy but works with column insert but breaks my summary formula on Sheet1.

    function Me() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('F24:F29').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A:A').activate();
  spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 1);
  spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('B1').activate();
  spreadsheet.getRange('Sheet1!F24:F29').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('C8').activate();
};

Basically when I run that one, My SUM formula moves over a column... I have my data set points in A, start pasting in B... Formula, =SUM(Sheet2!$B$2:$AW$2). When I run it, B turns into C... Run it again, C turns into D, etc.

Thoughts?

user_7215091 · Answer

Move from sheet1 to sheet2

function move() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName("Sheet1");
  const svs = ssh.getDataRange().getValues();
  const tsh = ss.getSheetByName("Sheet2");
  tsh.getRange(tsh.getLastRow() + 1,1,svs.length, svs[0].length).setValues(svs);
  ssh.clearContents();
}

Copy Data from Sheet1 and Paste in Sheet 2

1 Answers

Move from sheet1 to sheet2