With Google Sheets, move data from one spreadsheet to another spreadsheet after last row

Question

This code works as is, but it also adds the blank rows -- if there are 4 rows with data and 4 blank rows, the script adds 4 blank rows after the data is copied to the new sheet.

I need it to only copy the row with data, and not add the blank rows on the new sheet.

    function saveToData() {
       var ss, s, r, v, target,ts,tss;
       ss = SpreadsheetApp.getActive();
       s = ss.getSheetByName('Entry');
    if (s.getRange(3, 1).getValue()) {
       r = s.getDataRange()
        .offset(2, 0, s.getLastRow()-1,12);
       v = r.getValues();
       r.clear()
     tts = SpreadsheetApp.openById('10_XEaQiR71QN_90nIIIvDAU6un1KuOhi9-- 
     AVVtk5FI'); destination ID
     ts = tts.getSheetByName('Data');destination  Sheet 
     tab name
    ts.getLastRow()+1
    .setValues(v);



  }


 }

I think that if you provide the sample output and input you want, it will help users think of your solution. — Tanaike
Hi Tanaike thanks for responding basically what i need if for the script to take the data from sheet 1 cell A2 to column 12 find the last row with data and copy that data on a different spreadsheet on sheet 2 after the last row of data. 1. from sheet 1 copy data range from A2 to last row with data — Yvan L
2. find last row on sheet 2 on another spreadsheet 3. paste data collected from sheet 1 after last row 4. clear data from sheet 1 — Yvan L
Thank you for your response. You want to move the values of "A2:L" of "Sheet1" to the last row of "Sheet2". If my understanding correct, can I ask you about if (s.getRange(3, 1).getValue()) { in your script? I would like to try to think of your solution after I could understand what you want. I'm really sorry for my poor English skill. — Tanaike
Sorry it should of been from A3:L that's why it's getRange(3,1) also to verify that there is a value in cell A3 before proceeding thanks — Yvan L

Tanaike Tanaike · Accepted Answer · 2018-07-05T02:35:58

I could understand what you want to do as follows.

You want to move the values of "A3:L" of "Sheet1" (Entry) to the last row of "Sheet2" (Data), if the value of "A3" is existing.

If this is correct, how about this modification?

Modification points :

Used "A3:L" for the range for retrieving values.
Removed the empty rows from the values of "A3:L".
Put the values to the sheet of "Data" using setValues().

Modified script :

function saveToData() {
  var ss, s, r, v, target,ts,tss;
  ss = SpreadsheetApp.getActive();
  s = ss.getSheetByName('Entry');
  if (s.getRange(3, 1).getValue()) {
    r = s.getRange("A3:L"); // Modified
    v = r.getValues().filter(function(e){return e.filter(String).length > 0}); // Modified
    r.clear();
    tts = SpreadsheetApp.openById('10_XEaQiR71QN_90nIIIvDAU6un1KuOhi9--AVVtk5FI'); // destination ID
    ts = tts.getSheetByName('Data'); // destination Sheet tab name
    ts.getRange(ts.getLastRow()+1, 1, v.length, v[0].length).setValues(v); // Modified
  }
}

Other pattern :

You can also use this script.

function saveToData3() {
  var ss, s, r, v, target,ts,tss;
  ss = SpreadsheetApp.getActive();
  s = ss.getSheetByName('Entry');
  if (s.getRange(3, 1).getValue()) {
    tts = SpreadsheetApp.openById('10_XEaQiR71QN_90nIIIvDAU6un1KuOhi9--AVVtk5FI'); // destination ID
    ts = tts.getSheetByName('Data'); // destination Sheet tab name
    s.getRange("A3:L").moveTo(ts.getRange(ts.getLastRow()+1, 1)); // Added
  }
}

Note :

This script modified your script. So please be careful about the sheet ID and sheet names.

Reference :

If this was not what you want, please tell me. I would like to modify it.