CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mike K
Mike K

Reputation: 99

Copy data from one Sheet to another in Google App Script and append a row, one small issue

Sure I had this down but just can't get it right. Had a script set up to get data from one sheet and put it into another one, which I have done but it leaves gaps when copying and I can't figure out how to solve it. I'm sure in the code, its where I have put a question mark, is where the problem lies.

I have tried put i, last, last+1, 10, 12 but none of these work, feel like I'm missing something small to get this right. Below is the code a link to view the sheet if needed (the sheet is just for me to learn from, a basic example if you will).

Thanks in advance and also if the code could be better written, please just let me know as still learning this :)

function copyInfo() {
  var app = SpreadsheetApp;
  var copySheet = app.getActiveSpreadsheet().getSheetByName("Copy");
  for (var i = 2; i <12; i++) {   
  var getInfo = copySheet.getRange(2,2,i,2).getValues();
  //  get the info from range above - start at row 2 on column 2 (b), get number of rows i , number of columns = 2, b,c 
  var last = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paste").getLastRow();
  var pasteSheet = app.getActiveSpreadsheet().getSheetByName("Paste");
//  Tell it where you want the info to go to 
  
  pasteSheet.getRange(last+1,1,?,2).setValues(getInfo);
  var clearIt = copySheet.getRange(2,2,i,2).clearContent();  
// this clears the copy range aka getInfo
  }}

link to sheet

Upvotes: 2

Views: 47341

Answers (2)

Eduardo Cuomo
Eduardo Cuomo

Reputation: 19016

From answer https://stackoverflow.com/a/44967382/717267, I created the following version. That is copying dynamically all table data into other sheet.

function copyInfo() {
   var response = Browser.msgBox(
    'Confirm',
    'Copy all data?',
    Browser.Buttons.YES_NO
  );

  if (response === 'yes') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // Source sheet
    var copySheet = ss.getSheetByName("Copy");
    // Destination sheet
    var pasteSheet = ss.getSheetByName("Paste");

    // First data row number
    var firstDataRow = 3;
    // Get last data row
    var lastRow = copySheet.getLastRow();
    // Get last column
    var lastColumn = copySheet.getLastColumn();

    // Get source range
    var source = copySheet.getRange(firstDataRow, 1, lastRow - firstDataRow, lastColumn);
    // Get destination range
    var destination = pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1);

    // Add rows to copy data
    let maxRows = pasteSheet.getMaxRows();
    if ((pasteSheet.getLastRow() + lastRow) >= maxRows) {
      pasteSheet.insertRowsAfter(maxRows, lastRow - firstDataRow);
    }

    // Copy values to destination range
    source.copyTo(destination, { contentsOnly: true });

    Browser.msgBox(
      'Done!',
      'Operation finished.',
      Browser.Buttons.OK
    );
  } else {
    Browser.msgBox(
      'Cancelled!',
      'Operation cancelled!',
      Browser.Buttons.OK
    );
  }
}

Upvotes: 0

user555121
user555121

Reputation:

You can copy whole range at once using copyTo, so your function could be rewritten as:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Copy");
  var pasteSheet = ss.getSheetByName("Paste");

  // get source range
  var source = copySheet.getRange(2,2,12,2);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
}

Upvotes: 9

Related Questions