Ndtyjky

I have the following script running on a document (I found on the forum and unfortunately I cannot remember who it was to give credit to but it works great), pulling the whole rows data into a new sheet then deleting the row from its current location;

function onEdit(event)

{

    // assumes source data in sheet named Schedule

    // target sheet of move to named Invoiced

    // test column with "9 - Invoiced" is col 1

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var s = event.source.getActiveSheet();

    var r = event.source.getActiveRange();



    if(s.getName() == "AllOpps" && r.getColumn() == 1 && r.getValue() == 

"Outreached")

    {

        var row = r.getRow();

        var numColumns = s.getLastColumn();

        var targetSheet = ss.getSheetByName("InitialOutreach");

        if(targetSheet.getLastRow()+1 == targetSheet.getMaxRows())

        {

            targetSheet.insertRowsAfter(targetSheet.getLastRow(), 5); 

            //inserts 5 rows after last used row

        }

        var target = targetSheet.getRange(targetSheet.getLastRow()+1, 1);

        s.getRange(row, 1, 1, numColumns).moveTo(target);

        s.deleteRow(row);

    }

} 

What I am looking for is an amendment to this and I have no idea how to make it work as my script knowledge is extremely limited, in fact, I am not even sure if it is possible but here goes.

Upon selection of "Outreached" I would like that row to do 2 things, firstly for the whole row to be copied across to the InitialOutreach tab, as it does now, and at the same time column 11,12,13 and 14 to be copied into a completely different document, for ease of use I have added it in the shared sample doc as Mail. This second copy will need to be into the respectively named columns

You can find a sample sheet here. Please, note that AllOpps and Initial Outreach are in the same document and represents the initial transfer while Mail Merge tab will be the second transfer. Once again the second transfer ie. only select columns will be in a completely different document.
https://docs.google.com/spreadsheets/d/1x_NFLXC2doWAgCWcAbxpuAh1vW-IclMZ1prN4loWdDA/edit?usp=sharing.

I hope this is clear. Once again I am not even sure running 2 different tasks on 1 trigger is even possible but any efforts are highly appreciated.
Best,
J

NOTE: This reply has been modified from previous versions, to deal with various changes and problems as reported by the OP.

The code below should do what you need. Note carefully the changed function name myOnEdit which also appears in the onOpen event, where it is named as an "installable" trigger. See later notes on reason for this.

 function onOpen() {

  ScriptApp.newTrigger('myOnEdit')

   .onEdit()

   .create();

}



function myOnEdit(event)

{

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var s = event.source.getActiveSheet();

    var r = event.source.getActiveRange();



    if(s.getName() == "AllOpps" && r.getColumn() == 1 && r.getValue() == "Outreached")

    {

        var row = r.getRow();

        var numColumns = s.getLastColumn();



        // first copy and paste the "Outreached" row in entrity

        var targetSheet = ss.getSheetByName("InitialOutreach");

        if(targetSheet.getLastRow()+1 == targetSheet.getMaxRows())

        {

            targetSheet.insertRowsAfter(targetSheet.getLastRow(), 5); 

            //inserts 5 rows after last used row

        } 

        var target = targetSheet.getRange(targetSheet.getLastRow()+1, 1);

        var values = s.getRange(row, 11, 1, 5).getValues();   // save the values to copy to "mail Merge" 

        s.getRange(row, 1, 1, numColumns).moveTo(target);

        Logger.log(values);



        // Copy the required columns to "Mail Merge" spreadsheet"

        var mailMergeSS= SpreadsheetApp.openById('--copy your sheet ID here--');

        targetSheet = mailMergeSS.getSheetByName("Mail Merge");

        if(targetSheet.getLastRow()+1 == targetSheet.getMaxRows())

        {

            targetSheet.insertRowsAfter(targetSheet.getLastRow(), 5); 

            //inserts 5 rows after last used row

        }

        target = targetSheet.getRange(targetSheet.getLastRow()+1,1,1,5);

        target.setValues(values);



        s.deleteRow(row);

    }

}

1) Make the new target spreadsheet for the mail merge lines and make a new tag called "Mail Merge". Make

2) To find the ID of the new sheet open it and look at the URL. It should look something like this:
https://docs.google.com/spreadsheets/d/14NP6TUyj1xLCPjKsPdGS6fiO0Bvzv1u4696LFd3tA7I/edit#gid=0

The ID is the emboldened bit ie 14NP6TUyj1xLCPjKsPdGS6fiO0Bvzv1u4696LFd3tA7I in the example above,. Yours will be different, so copy and paste it so that your code line looks like:

var mailMergeSS= SpreadsheetApp.openById('14NP6TUyj1xLCPjKsPdGS6fiO0Bvzv1u4696LFd3tA7I');

To avoid this error:

SpreadsheetApp.openById([0AjqSnE_p3nFqdDN0LWpFbjFqVDRwNmFGOV91QzZrZc])
[0 seconds] Execution failed: You do not have permission to perform
that action.

.. which arises when you try to open an external sheet, you need to use "Installable Triggers". The appropriate references are here:

www.developers.google.com/apps-script/guides/triggers/installable

developers.google.com/apps-script/guides/triggers/events

Installable triggers can be installed in 2 ways:

1. In your CopyCols sheet, go to the script editor and select Current
   Project Triggers. Add a new trigger with settings as follows: Choose
   which function to run: myOnEdit Which runs at deployment:
   head Select Event Source: from Spreadsheet Select
   event type: onEdit Then save and restart your sheet..


2. or....In your code add this function lines:

function onOpen() {
ScriptApp.newTrigger('myOnEdit')
.onEdit()
.create();
}