running script on an Inactive sheet










0















I have been using the following script to move "Finished" columns from one sheet to another:



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

if(s.getName() == "Sheet1" && r.getColumn() == 15 && r.getValue() ==
"Finished")
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);




Im trying to figure out how to get the script to run even if the sheet has not been opened or edited. Im just not sure how to go about changing it to use a time trigger every minute or so.










share|improve this question


























    0















    I have been using the following script to move "Finished" columns from one sheet to another:



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

    if(s.getName() == "Sheet1" && r.getColumn() == 15 && r.getValue() ==
    "Finished")
    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);




    Im trying to figure out how to get the script to run even if the sheet has not been opened or edited. Im just not sure how to go about changing it to use a time trigger every minute or so.










    share|improve this question
























      0












      0








      0








      I have been using the following script to move "Finished" columns from one sheet to another:



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

      if(s.getName() == "Sheet1" && r.getColumn() == 15 && r.getValue() ==
      "Finished")
      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);




      Im trying to figure out how to get the script to run even if the sheet has not been opened or edited. Im just not sure how to go about changing it to use a time trigger every minute or so.










      share|improve this question














      I have been using the following script to move "Finished" columns from one sheet to another:



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

      if(s.getName() == "Sheet1" && r.getColumn() == 15 && r.getValue() ==
      "Finished")
      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);




      Im trying to figure out how to get the script to run even if the sheet has not been opened or edited. Im just not sure how to go about changing it to use a time trigger every minute or so.







      google-sheets triggers eventtrigger






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 14 '18 at 17:08









      chris kesterchris kester

      1




      1






















          1 Answer
          1






          active

          oldest

          votes


















          0














          There are two aspects to the change to a timed trigger from onEdit.

          The first concerns revisions to the code, the second is the trigger details.



          Code

          The code can't be re-used because the timed trigger doesn't provide the same event details as OnEdit.

          In addition, it is possible that several rows might be tagged "Finished" between each trigger event, and the code needs to respond to them all. lastly, each "finished" row can't be deleted as it is found, because this affects the row number of all remaining rows in the column.



          The following code would do the job:

          Most of it will be familiar to the questioner. The main except is to keep a record of each row number that is moved to "Finished". This is done by pushing the row number onto an array. Then after all data has been examined and moved, there is a small loop that takes the row numbers recorded in the array and deletes the relevant row. The loop works from the highest row number to the lowest; this is so that the deletion of a row does not affect the row number of any remaining rows to be deleted.




          function so_53305432() 

          // set up the spreadsheet
          var ss = SpreadsheetApp.getActiveSpreadsheet();

          // identify source and target sheets
          var sourceSheet = ss.getSheetByName("Sheet1");
          var targetSheet = ss.getSheetByName("Finished");

          // get some variables to use as ranges
          var sourcelastRow = sourceSheet.getLastRow();
          var numColumns = sourceSheet.getLastColumn();
          var targetLastRow = targetSheet.getLastRow();

          // get data from the Source sheet
          var sourceData = sourceSheet.getRange(1, 1, sourcelastRow, numColumns).getValues();

          // set up some variables
          var finishedRows = ;
          var i = 0;
          var x = 0;
          var temp = 0;


          // loop through column 15 (O) checking for value = "Finished"
          for (i = 0; i < sourcelastRow; i++)

          // If value = Finished
          if (sourceData[i][14] == "Finished")

          // define the target range and move the source row
          var targetLastRow = targetSheet.getLastRow();
          var target = targetSheet.getRange(targetLastRow + 1, 1);
          sourceSheet.getRange(+i + 1, 1, 1, numColumns).moveTo(target);

          // keep track of the source row number.
          finishedRows.push(i);



          // set up variables for loop though the rows to be deleted
          var finishedLength = finishedRows.length;
          var startcount = finishedLength - 1

          // loop throught the array to delete rows; start with the highest row# first
          for (x = startcount; x > -1; x--)
          // get the row number for the script
          temp = +finishedRows[x] + 1;
          // delete the row
          sourceSheet.deleteRow(temp);






          Trigger

          The trigger needs to be revised. To do this:

          1) Open the script editor, select Current Project Triggers. OnEdit should appear as an existing trigger with an event type of OnEdit.

          2) Change "Choose which function to run" to the new function,

          3) Change "Select Event Source" from Spreadsheet to "Time Driven".

          4) Select "Type of time based trigger" = "Minutes Timer".

          5) Select "Select Minute Interval" = , and select a time period and interval.

          6) Save the trigger, and then close the Trigger tab



          If "Every Minute" is found to be too often, then the Questioner could try "Every 5 minutes".




          Trigger before




          Trigger after








          share|improve this answer
























            Your Answer






            StackExchange.ifUsing("editor", function ()
            StackExchange.using("externalEditor", function ()
            StackExchange.using("snippets", function ()
            StackExchange.snippets.init();
            );
            );
            , "code-snippets");

            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "1"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













            draft saved

            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305432%2frunning-script-on-an-inactive-sheet%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            There are two aspects to the change to a timed trigger from onEdit.

            The first concerns revisions to the code, the second is the trigger details.



            Code

            The code can't be re-used because the timed trigger doesn't provide the same event details as OnEdit.

            In addition, it is possible that several rows might be tagged "Finished" between each trigger event, and the code needs to respond to them all. lastly, each "finished" row can't be deleted as it is found, because this affects the row number of all remaining rows in the column.



            The following code would do the job:

            Most of it will be familiar to the questioner. The main except is to keep a record of each row number that is moved to "Finished". This is done by pushing the row number onto an array. Then after all data has been examined and moved, there is a small loop that takes the row numbers recorded in the array and deletes the relevant row. The loop works from the highest row number to the lowest; this is so that the deletion of a row does not affect the row number of any remaining rows to be deleted.




            function so_53305432() 

            // set up the spreadsheet
            var ss = SpreadsheetApp.getActiveSpreadsheet();

            // identify source and target sheets
            var sourceSheet = ss.getSheetByName("Sheet1");
            var targetSheet = ss.getSheetByName("Finished");

            // get some variables to use as ranges
            var sourcelastRow = sourceSheet.getLastRow();
            var numColumns = sourceSheet.getLastColumn();
            var targetLastRow = targetSheet.getLastRow();

            // get data from the Source sheet
            var sourceData = sourceSheet.getRange(1, 1, sourcelastRow, numColumns).getValues();

            // set up some variables
            var finishedRows = ;
            var i = 0;
            var x = 0;
            var temp = 0;


            // loop through column 15 (O) checking for value = "Finished"
            for (i = 0; i < sourcelastRow; i++)

            // If value = Finished
            if (sourceData[i][14] == "Finished")

            // define the target range and move the source row
            var targetLastRow = targetSheet.getLastRow();
            var target = targetSheet.getRange(targetLastRow + 1, 1);
            sourceSheet.getRange(+i + 1, 1, 1, numColumns).moveTo(target);

            // keep track of the source row number.
            finishedRows.push(i);



            // set up variables for loop though the rows to be deleted
            var finishedLength = finishedRows.length;
            var startcount = finishedLength - 1

            // loop throught the array to delete rows; start with the highest row# first
            for (x = startcount; x > -1; x--)
            // get the row number for the script
            temp = +finishedRows[x] + 1;
            // delete the row
            sourceSheet.deleteRow(temp);






            Trigger

            The trigger needs to be revised. To do this:

            1) Open the script editor, select Current Project Triggers. OnEdit should appear as an existing trigger with an event type of OnEdit.

            2) Change "Choose which function to run" to the new function,

            3) Change "Select Event Source" from Spreadsheet to "Time Driven".

            4) Select "Type of time based trigger" = "Minutes Timer".

            5) Select "Select Minute Interval" = , and select a time period and interval.

            6) Save the trigger, and then close the Trigger tab



            If "Every Minute" is found to be too often, then the Questioner could try "Every 5 minutes".




            Trigger before




            Trigger after








            share|improve this answer





























              0














              There are two aspects to the change to a timed trigger from onEdit.

              The first concerns revisions to the code, the second is the trigger details.



              Code

              The code can't be re-used because the timed trigger doesn't provide the same event details as OnEdit.

              In addition, it is possible that several rows might be tagged "Finished" between each trigger event, and the code needs to respond to them all. lastly, each "finished" row can't be deleted as it is found, because this affects the row number of all remaining rows in the column.



              The following code would do the job:

              Most of it will be familiar to the questioner. The main except is to keep a record of each row number that is moved to "Finished". This is done by pushing the row number onto an array. Then after all data has been examined and moved, there is a small loop that takes the row numbers recorded in the array and deletes the relevant row. The loop works from the highest row number to the lowest; this is so that the deletion of a row does not affect the row number of any remaining rows to be deleted.




              function so_53305432() 

              // set up the spreadsheet
              var ss = SpreadsheetApp.getActiveSpreadsheet();

              // identify source and target sheets
              var sourceSheet = ss.getSheetByName("Sheet1");
              var targetSheet = ss.getSheetByName("Finished");

              // get some variables to use as ranges
              var sourcelastRow = sourceSheet.getLastRow();
              var numColumns = sourceSheet.getLastColumn();
              var targetLastRow = targetSheet.getLastRow();

              // get data from the Source sheet
              var sourceData = sourceSheet.getRange(1, 1, sourcelastRow, numColumns).getValues();

              // set up some variables
              var finishedRows = ;
              var i = 0;
              var x = 0;
              var temp = 0;


              // loop through column 15 (O) checking for value = "Finished"
              for (i = 0; i < sourcelastRow; i++)

              // If value = Finished
              if (sourceData[i][14] == "Finished")

              // define the target range and move the source row
              var targetLastRow = targetSheet.getLastRow();
              var target = targetSheet.getRange(targetLastRow + 1, 1);
              sourceSheet.getRange(+i + 1, 1, 1, numColumns).moveTo(target);

              // keep track of the source row number.
              finishedRows.push(i);



              // set up variables for loop though the rows to be deleted
              var finishedLength = finishedRows.length;
              var startcount = finishedLength - 1

              // loop throught the array to delete rows; start with the highest row# first
              for (x = startcount; x > -1; x--)
              // get the row number for the script
              temp = +finishedRows[x] + 1;
              // delete the row
              sourceSheet.deleteRow(temp);






              Trigger

              The trigger needs to be revised. To do this:

              1) Open the script editor, select Current Project Triggers. OnEdit should appear as an existing trigger with an event type of OnEdit.

              2) Change "Choose which function to run" to the new function,

              3) Change "Select Event Source" from Spreadsheet to "Time Driven".

              4) Select "Type of time based trigger" = "Minutes Timer".

              5) Select "Select Minute Interval" = , and select a time period and interval.

              6) Save the trigger, and then close the Trigger tab



              If "Every Minute" is found to be too often, then the Questioner could try "Every 5 minutes".




              Trigger before




              Trigger after








              share|improve this answer



























                0












                0








                0







                There are two aspects to the change to a timed trigger from onEdit.

                The first concerns revisions to the code, the second is the trigger details.



                Code

                The code can't be re-used because the timed trigger doesn't provide the same event details as OnEdit.

                In addition, it is possible that several rows might be tagged "Finished" between each trigger event, and the code needs to respond to them all. lastly, each "finished" row can't be deleted as it is found, because this affects the row number of all remaining rows in the column.



                The following code would do the job:

                Most of it will be familiar to the questioner. The main except is to keep a record of each row number that is moved to "Finished". This is done by pushing the row number onto an array. Then after all data has been examined and moved, there is a small loop that takes the row numbers recorded in the array and deletes the relevant row. The loop works from the highest row number to the lowest; this is so that the deletion of a row does not affect the row number of any remaining rows to be deleted.




                function so_53305432() 

                // set up the spreadsheet
                var ss = SpreadsheetApp.getActiveSpreadsheet();

                // identify source and target sheets
                var sourceSheet = ss.getSheetByName("Sheet1");
                var targetSheet = ss.getSheetByName("Finished");

                // get some variables to use as ranges
                var sourcelastRow = sourceSheet.getLastRow();
                var numColumns = sourceSheet.getLastColumn();
                var targetLastRow = targetSheet.getLastRow();

                // get data from the Source sheet
                var sourceData = sourceSheet.getRange(1, 1, sourcelastRow, numColumns).getValues();

                // set up some variables
                var finishedRows = ;
                var i = 0;
                var x = 0;
                var temp = 0;


                // loop through column 15 (O) checking for value = "Finished"
                for (i = 0; i < sourcelastRow; i++)

                // If value = Finished
                if (sourceData[i][14] == "Finished")

                // define the target range and move the source row
                var targetLastRow = targetSheet.getLastRow();
                var target = targetSheet.getRange(targetLastRow + 1, 1);
                sourceSheet.getRange(+i + 1, 1, 1, numColumns).moveTo(target);

                // keep track of the source row number.
                finishedRows.push(i);



                // set up variables for loop though the rows to be deleted
                var finishedLength = finishedRows.length;
                var startcount = finishedLength - 1

                // loop throught the array to delete rows; start with the highest row# first
                for (x = startcount; x > -1; x--)
                // get the row number for the script
                temp = +finishedRows[x] + 1;
                // delete the row
                sourceSheet.deleteRow(temp);






                Trigger

                The trigger needs to be revised. To do this:

                1) Open the script editor, select Current Project Triggers. OnEdit should appear as an existing trigger with an event type of OnEdit.

                2) Change "Choose which function to run" to the new function,

                3) Change "Select Event Source" from Spreadsheet to "Time Driven".

                4) Select "Type of time based trigger" = "Minutes Timer".

                5) Select "Select Minute Interval" = , and select a time period and interval.

                6) Save the trigger, and then close the Trigger tab



                If "Every Minute" is found to be too often, then the Questioner could try "Every 5 minutes".




                Trigger before




                Trigger after








                share|improve this answer















                There are two aspects to the change to a timed trigger from onEdit.

                The first concerns revisions to the code, the second is the trigger details.



                Code

                The code can't be re-used because the timed trigger doesn't provide the same event details as OnEdit.

                In addition, it is possible that several rows might be tagged "Finished" between each trigger event, and the code needs to respond to them all. lastly, each "finished" row can't be deleted as it is found, because this affects the row number of all remaining rows in the column.



                The following code would do the job:

                Most of it will be familiar to the questioner. The main except is to keep a record of each row number that is moved to "Finished". This is done by pushing the row number onto an array. Then after all data has been examined and moved, there is a small loop that takes the row numbers recorded in the array and deletes the relevant row. The loop works from the highest row number to the lowest; this is so that the deletion of a row does not affect the row number of any remaining rows to be deleted.




                function so_53305432() 

                // set up the spreadsheet
                var ss = SpreadsheetApp.getActiveSpreadsheet();

                // identify source and target sheets
                var sourceSheet = ss.getSheetByName("Sheet1");
                var targetSheet = ss.getSheetByName("Finished");

                // get some variables to use as ranges
                var sourcelastRow = sourceSheet.getLastRow();
                var numColumns = sourceSheet.getLastColumn();
                var targetLastRow = targetSheet.getLastRow();

                // get data from the Source sheet
                var sourceData = sourceSheet.getRange(1, 1, sourcelastRow, numColumns).getValues();

                // set up some variables
                var finishedRows = ;
                var i = 0;
                var x = 0;
                var temp = 0;


                // loop through column 15 (O) checking for value = "Finished"
                for (i = 0; i < sourcelastRow; i++)

                // If value = Finished
                if (sourceData[i][14] == "Finished")

                // define the target range and move the source row
                var targetLastRow = targetSheet.getLastRow();
                var target = targetSheet.getRange(targetLastRow + 1, 1);
                sourceSheet.getRange(+i + 1, 1, 1, numColumns).moveTo(target);

                // keep track of the source row number.
                finishedRows.push(i);



                // set up variables for loop though the rows to be deleted
                var finishedLength = finishedRows.length;
                var startcount = finishedLength - 1

                // loop throught the array to delete rows; start with the highest row# first
                for (x = startcount; x > -1; x--)
                // get the row number for the script
                temp = +finishedRows[x] + 1;
                // delete the row
                sourceSheet.deleteRow(temp);






                Trigger

                The trigger needs to be revised. To do this:

                1) Open the script editor, select Current Project Triggers. OnEdit should appear as an existing trigger with an event type of OnEdit.

                2) Change "Choose which function to run" to the new function,

                3) Change "Select Event Source" from Spreadsheet to "Time Driven".

                4) Select "Type of time based trigger" = "Minutes Timer".

                5) Select "Select Minute Interval" = , and select a time period and interval.

                6) Save the trigger, and then close the Trigger tab



                If "Every Minute" is found to be too often, then the Questioner could try "Every 5 minutes".




                Trigger before




                Trigger after









                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 18 '18 at 7:36

























                answered Nov 18 '18 at 7:29









                TedinozTedinoz

                1,19821118




                1,19821118





























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305432%2frunning-script-on-an-inactive-sheet%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    這個網誌中的熱門文章

                    How to read a connectionString WITH PROVIDER in .NET Core?

                    In R, how to develop a multiplot heatmap.2 figure showing key labels successfully

                    Museum of Modern and Contemporary Art of Trento and Rovereto