CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Pfeff
Pfeff

Reputation: 1

Move a row of data from one spreadsheet to another

I'm new to the world of javascript & google apps script and I'm struggling to get a row of data to move from one spreadsheet to another. I have it moving a row from sheet to sheet within the same spreadsheet but not across spreadsheets.

I know I need to use SpreadsheetApp.openById("insertID") rather than SpreadsheetApp.getActiveSpreadsheet() but I can't seem to make it work.

Here is the code I have that's working based on moving sheet to sheet, how do I modify it to move to a new spreadsheet?

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

  if(s.getName() == "Timeline Submit" && r.getColumn() == 117 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("WIP");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target, {contentsOnly:true});
      } else if(s.getName() == "WIP" && r.getColumn() == 117 && r.getValue() == false) {
    var row = r.getRow();
        var numColumns = s.getColumn();
    var targetSheet = ss.getSheetByName("Timeline Submit");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target, {contentsOnly:true});
      }
}

Upvotes: 0

Views: 858

Answers (1)

Cooper
Cooper

Reputation: 64032

I couldn't figure out everything in the second section of your code but it had too many errors so I just guessed and some of it.

Don't forget to go create a trigger for this in the Script Editor Edits/Current Project Triggers.

function installableOnEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=="Timeline Submit" && e.range.columnStart==117 && e.value==true) {
    var ss=SpreadsheetApp.openById('id')
    var tsh= ss.getSheetByName("WIP");
    var trg=tsh.getRange(tsh.getLastRow()+1,1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).copyTo(trg,{contentsOnly:true})
  }else if(sh.getName()=="WIP" && e.range.columnStart==17 && e.value==false) {
    var ss=SpreadsheetApp.openById('id')
    var tsh=ss.getSheetByName('Timeline Submit');
    var trg=sh.getRange(tsh.getLastRow()+1,1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).copyTo(trg,{contentsOnly:true});
  }
}

Upvotes: 1

Related Questions