Add data to a spreadsheet from another using script.

Question

I iterate through the rows and get the value of a certain cell like so:

for (var i = 2; i < lastRow; i++) {
    if (sheet.getRange(i, getColIndexByName("Department Concerned")).getValue() == "HR") {
      // Do some stuff
    }
  }

I copy data to another sheet like so:

// select the Spreadsheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("Spreadsheet ID");

  // select the Sheets from the Spreadsheets
  var source_sheet = ss.getSheetByName("Logs");
  var target_sheet = target.getSheetByName("Sheet3");

  // select the range from the Sheets
  var source_range = source_sheet.getRange("A2:J500");
  var target_range = target_sheet.getRange("A2:J500");

  // get the values from the source sheet
  var values = source_range.getValues();

  // set the values to the destination sheet
  target_range.setValues(values);

What I need is to copy that data and add it to the same range of the other Spreadsheet depending on the cell value that I just took in my "for loop". Thank you.

what can we help you with here? the two sniped of code you show look to be working, just include one in the other. Is there something more you didn't write about here? — Harold

iJay iJay · Accepted Answer · 2016-08-25T05:54:06

What you think was right, but need to find better approach.

Select values from source sheet
Check each rows, column Department Concerned for "HR" value
Select rows that fulfill above condition
Paste it in target sheet

I combined those scenarios as this,

function myFunction() 
{
 // select the Spreadsheets
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var target = SpreadsheetApp.openById("Spreadsheet ID");

 // select the Sheets from the Spreadsheets
 var source_sheet = ss.getSheetByName("Logs");
 var target_sheet = target.getSheetByName("Sheet3");

 // select the range from the Sheets
 var source_range = source_sheet.getRange("A2:J500");

 // get the values from the source sheet
 var values = source_range.getValues();

 // an array for selected rows
 var selectedRows = []; 

 for(var row in values)
 {
  // thinking 5th column is Department Concerned
  if(values[row][4] =='HR')
  {
    //add add entire row to selected 2d array 
    selectedRows.push(values[row]); 
  }
 }
 // set the values to the destination sheet
 target_sheet.getRange(2, 1, selectedRows.length,10).setValues(selectedRows);  
}

Add data to a spreadsheet from another using script.

1 Answers