CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user13509354
user13509354

Reputation:

Copy multiple rows from a spreadsheet to another spreadsheet

I'm writing a script that copy a selected row from a spreadsheet to another spreadsheet, from the column A to the column D.
It works fine.
The problems are 3:

  1. How to change the columns from A to D with the columns C and E.
  2. The script works only with one row selected, how can I copy multiple rows selected at the same time.
  3. When the script is terminated, I would that in column H appears the value Sent for the rows copied.

Thanks in advance.

function main() {

transfer("....", "Foglio1", "Foglio1");

}
function transfer(targetId, sourceSheetName, targetSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var last = sourceSheet.getActiveRange().getRow();
  var data = sourceSheet.getRange(last, 1, 1, 4).getValues();

  // copy data
  var ss2 = SpreadsheetApp.openById(targetId);
  var targetSheet = ss2.getSheetByName(targetSheetName);
  //get last row
  var lastRow = targetSheet.getLastRow();
  //write data
  targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length)
           .setValues(data);
}

Upvotes: 0

Views: 241

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

  1. How to change the columns from A to D with the columns C and E?

Please have a look at the arguments of getRange:

Replace

var data = sourceSheet.getRange(last, 1, 1, 4).getValues();

with

var data = sourceSheet.getRange(last, 3, 1, 3).getValues();

  1. The script works only with one row selected, how can I copy multiple rows selected at the same time?

You can use getHeight to find the number of rows selected and pass it to getRange:

var height = sourceSheet.getActiveRange().getHeight();  
var data = sourceSheet.getRange(last, 3, height, 3).getValues();
  1. When the script is terminated, in column H appears the value "Sent" for the rows copied.

You can just set the values of column H to Sent for the selected rows:

sourceSheet.getRange(last, 8, height).setValue('Sent');

Solution:

function main() {

transfer("....", "Foglio1", "Foglio1");

}

function transfer(targetId, sourceSheetName, targetSheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var last = sourceSheet.getActiveRange().getRow();
  var height = sourceSheet.getActiveRange().getHeight();  
  var data = sourceSheet.getRange(last, 3, height, 3).getValues();

  // copy data
  var ss2 = SpreadsheetApp.openById(targetId);
  var targetSheet = ss2.getSheetByName(targetSheetName);
  //get last row
  var lastRow = targetSheet.getLastRow();
  //write data
  targetSheet.getRange(lastRow + 1, 3, data.length, data[0].length)
           .setValues(data);
  
  sourceSheet.getRange(last, 8, height).setValue('Sent');
}

Upvotes: 2

Related Questions