Google Sheet Copy rows to different spreadsheets

Question

I put together this test:

https://docs.google.com/a/strandvinduspuss.no/spreadsheets/d/1WZTYq-WiQq8asXZjHMMN9bsGiQKbPKmlRrsnkAsadU4/edit?usp=sharing

The function: in the List tab, when you change the name of col B, part of the row is copied to the matching sheet.

What I need is to copy them to separate spreadsheets (one for each employer). Is that possible?

My code:

// http://victorwyee.com/gapps/move-row-on-cell-value-google-spreadsheets/
   /**
    * Moves row of data to another spreadsheet based on criteria in column 6.
    * Each spreadsheet can be a "source" spreadsheet, or the "target"    spreadsheet.
    * Assumes there as many spreadsheets as there are criteria values, e.g.
    *   if the criteria are "R", "P", and "D", then there are three spreadsheets
    *   named "R", "P" and "D".
    * Assumes that each spreadsheet has the same structure.
    */

  function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  // Get the row and column of the active cell.
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();

  // Get the number of columns in the active sheet.
  var colNumber = s.getLastColumn();

   // Move row based on criteria in column 6, and if row is not the header.
  if (colIndex == 2 && rowIndex != 1) {

    // Get value from column 6, in the active row.
    var status = s.getRange(rowIndex, colIndex).getValue();

    // Do nothing if criteria value is not actually changed to something else.
    if (s.getName() != status) { 

      // The target sheet is the one with the same name as the criteria value.
      var targetSheet = ss.getSheetByName(status);
      var target = targetSheet.getRange(targetSheet.getLastRow() +1, 1);

      // Set the range and copy
      // s.getRange(rowIndex, 10, 1, colNumber).copyTo(target);
      targetSheet.insertRowsAfter(targetSheet.getLastRow(), 1);
      s.getRange(rowIndex, 1, 1, 16).copyTo(target);

      // s.deleteRow(rowIndex);
    }
  }
}

Have made some changes so the comments are not updated.

Jens Astrup Jens Astrup · Accepted Answer · 2015-09-03T12:26:08

You can copy sheets to other Spreadsheets, but (to my knowledge, based off of the documentation and errors I've received) you can not copy a range to a separate spreadsheet. I have a similar use case to you, and my workaround is to move what I want to copy to a separate sheet, and then copy that sheet to the new/other spreadsheet:

var newSpreadsheet = SpreadsheetApp.create("I am a copy");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy Me");
sheet.copyTo(newSpreadsheet);

This wouldn't work in your case if you needed to keep the same sheet/data where you are moving the data to however, since it would be overwritten. An option in that case is to copy the sheet over, and then move whatever data you want, and then delete the excess sheet.

var newSpreadsheet = SpreadsheetApp.create("I am a copy");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var projectname = SpreadsheetApp.getActiveSpreadsheet();
sheet.copyTo(newSpreadsheet);

var dest = newSpreadsheet.getSheetByName("Copy of Sheet1").getRange("E7");
newSpreadsheet.getSheetByName("Copy of Sheet1").getRange("B1").copyTo(dest);

var notNeeded = newSpreadsheet.getSheetByName("Copy of Sheet1");
newSpreadsheet.deleteSheet(notNeeded);

Hopefully that helps get you moving in the right direction...

Google Sheet Copy rows to different spreadsheets

2 Answers