Move a row between different spreadsheets - Google Spreadsheet

Question

I'm a beginner in coding and on SO, but I'm trying to understand what I'm doing and what is happening in the code.

I'm wanting to move a row between different spreadsheets.

I have a script in "CORE" Spreadsheet, which moves a row between the "Home" and "Finished" sheets Link to the spreadsheet:

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

  if (s.getName() == "Home" && r.getColumn() == 5 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Finished");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  else if (s.getName() == "Finished" && r.getColumn() == 5 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Home");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

I searched how to move the data located on "Finished" sheet to another spreedsheet called "DATA BASE" Link to the spreadsheet:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Finished");
  var range = sheet.getRange("A1:E2");
  var values = range.getValues();
  var allvals = [];
  for (var i = 0; i < values.length; i++) {
    allvals.push(values[i]) ;
  }

  var dataLength = allvals.length;
  // alert data
  var ui = SpreadsheetApp.getUi();
  // ui.alert( JSON.stringify(allvals) )

  // copy to another Google Spreadsheet
  var newSheet = SpreadsheetApp.openById('1Xn-hrPPr3tQwWMbKdWxHTXcazOqWu1bdHT6A5LZmlD8').getSheets()[0];
  var tmp = "A1:E" + dataLength;
  var newRange = newSheet.getRange(tmp);
  newRange.setValues(values);
}

But I want to transfer the data from sheet "CORE"/"Finished" to sheet "DATA BASE"/ "DB" without rewriting the other rows already on the DB sheet. My idea is to make the spreadsheet "DATA BASE" a real database, where other sheets put data in it and other sheets use this data.

Is it possible to move rows between different spreadsheets like in the first code? I know its impossible to use the .openById() with the function onEdit(), but the most important thing is to move the rows without rewriting the other rows already in the target sheet (DB).

Hi, welcome to Stack Overflow. By saying "move the rows without rewriting the other rows already on the target sheet" do you mean to add a new row with new content in it? — RCRalph
I'm pretty sure that you can use `SpreadsheetApp.openById(). If you switch to an installable trigger and change the name 'onEdit' to something else like onMyInstallableEdit() ... really any name but onEdit() so that you don't get two triggers firing the same function at the same time. — Cooper

Cooper Cooper · Accepted Answer · 2020-02-05T20:03:32

Moving a row to another spreadsheet with installable onEdit trigger

This is just an example of moving the data in a row from one spreadsheet to another with an installable onEdit Trigger. If you wish to play with it don't forget to add the target spreadsheet id.

function onMyEdit(e) {
  //e.source.toast('Entry');
  var sh=e.range.getSheet();
  //Logger.log(JSON.stringify(e));
  if(sh.getName()=='Sheet2' && e.range.columnStart==11 && e.value=="TRUE") {
    //e.source.toast('Access');
    e.range.setValue('FALSE');
    var tss=SpreadsheetApp.openById('SSID');
    var tsh=tss.getSheetByName('Sheet2');
    var v=sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()-1).getValues();
    Logger.log(v);
    tsh.getRange(tsh.getLastRow()+1,1,v.length,v[0].length).setValues(v);
    sh.deleteRow(e.range.rowStart);
  }
}

Move a row between different spreadsheets - Google Spreadsheet

1 Answers