Google Sheets - Pushing a row to a second spreadsheet based on a value being entered

Question

I'm new to this,

I have 2 google spreadsheets:

Spreadsheet A: The active sheet Containing multiple tabs with information to be Pushed to B. Spreadsheet B: A spreadsheet with a single tab. The same headers and structure as spreadsheet A.

Based on the user selecting the answer "Yes" in the first column of any of the 1 tabs in Spreadsheet A, I would like that entire row to move over to Spreadsheet B.

I have modified a script that works on a single spreadsheet (ie moving rows from tab to tab) to attempt to get it to work between spreadsheets:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tss = SpreadsheetApp.openById('B').getSheetByName('Sheet 1');
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(r.getColumn() == 1 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var target = tss.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
  }
}

Probably needless to say, this yields no result. Having searched through a number of posts and forums I only see individuals posting about how to move rows between tabs but not between entirely separate spreadsheets. Is it even possible to do this? If so, what am I doing wrong in the script?

Thank you so much for anyone who takes the time to assist.

Anthony

Possible yes, but not with a "simple trigger". Review your Apps Script trigger documentation — tehhowch
There is an alternative that does not use Google App Script but it depends on whether or not you want to keep the contents of Spreadsheet A secret - do you need to hide these rows? If so, then you need Google App Script to push as you have already begun trying. — Paul
Sorry for the delay jumping back on. @Paul I do not need the contents of Spreadsheet A to be a secret/to hide these rows. What were you thinking of as an alternative? — Anthony Hendrickson
You could use a combination of IMPORTRANGE() and QUERY(). E.g. =QUERY(IMPORTRANGE(url,range),"select A where B matches 'Yes'") or similar — Paul
Hey @Paul this is a completely practical solution. I made the changes and now it works. It turns out I was having endless problems in the background with my IT department cloud locking my scripts and this has served as an excellent solution. Thank you — Anthony Hendrickson

Paul Paul · Accepted Answer · 2018-11-14T17:42:04

Following a dialogue with the OP in the comments section, it was indicated that the original spreadsheet did not to be kept secret.

Consequently, the desired functionality can be provided by using a combination of IMPORTRANGE() and QUERY() in the spreadsheet with no need to use Google App Script. For instance,

=QUERY(IMPORTRANGE(url,range),"select A where B matches 'Yes'") or similar

This imports data from a second spreadsheet and then the QUERY() function acts as a way of filtering the imported range by certain criteria.

Once the imported range is authorised, the editors of the spreadsheet can access it by, e.g. removing or modifying the query. You could prevent this by protecting that particular cell, if needed.

Google Sheets - Pushing a row to a second spreadsheet based on a value being entered

1 Answers