Write remotely to another googlesheet

user_13205743 0
Viewed 83

I have the function below I have copied from somewhere else. It basically moves a row you would have completed to another sheet in the same file (moves from sheet Origin to Target, Trigger is "Move").

What I would like to achieve is to be able to move that completed row to another google sheet file instead of another sheet in the same file. I have tried SpreadsheetApp.openByUrl and SpreadsheetApp.openByID, but nothing happens, I was expecting some permission request etc. but nothing, not even an error. Can someone help me with this? 

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

  if(s.getName() == "Origin" && r.getColumn() == 10 && r.getValue() == "Move") {

    var row = r.getRow();
    var numColumns = s.getLastColumn();

    var targetSheet = ss.getSheetByName("Target");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, (numColumns-1)).moveTo(target);
    s.deleteRow(row);

  }
google-apps-script google-sheets
1 Answers

I believe your goal as follows.

  • You want to copy the values of s.getRange(row, 1, 1, (numColumns-1)) to the last row of Target sheet in other Spreadsheet.
  • You want to run the script by OnEdit event trigger.

For this, how about this answer?

Modification points:

  • In order to use SpreadsheetApp.openByUrl and SpreadsheetApp.openByID, it is required to use the installable OnEdit event trigger instead of the simple trigger of OnEdit event trigger. When SpreadsheetApp.openByUrl and SpreadsheetApp.openByID are run with the simple trigger, an error related to the permission occurs.
    • I think that the reason of your issue is this.
  • When the values are moved to other Spreadsheet, move() cannot be used. So in this case, getValues and setValues are used.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please copy and paste the following script. And, please install the OnEdit event trigger to the function of installedOnEdit as the installable trigger. By this, when the cell is edited, SpreadsheetApp.openById is run.

function installedOnEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Origin" && r.getColumn() == 10 && r.getValue() == "Move") {

    var id = "###";  // Please set the Spreadsheet ID.

    var dst = SpreadsheetApp.openById(id);
    var targetSheet = dst.getSheetByName("Target");
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var srcRange = s.getRange(row, 1, 1, (numColumns-1));
    s.deleteRow(row);
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns - 1).setValues(srcRange.getValues());
  }
}

References:

user_71086530
Related