Google Sheets - Script to move rows to different tab depending on a key word

Question

I have been working on a google sheet that that receives a form submission. Once the submission comes in I need a script to move the rows of data to different tabs depending on the name shown in column C.

I have extensively searched for solutions on stack overflow and I am very close to a solution right now through the code I have found and edited.

I have a script that will move historic dates to a different tab and leave all future dates in the original tab based in the logic of the date being older than today.

All I need to do now is modify this to move the rows with the name "John" in column C to the John tab and ignore the date.

Once I can get one name to work I am confident I can make this work for multiple names and multiple tabs.

Please feel free to create a copy of the following test sheet I have been working on.

link: https://docs.google.com/spreadsheets/d/1zJpylrD_5hzScW3lIjIQQSKiY0Aan6Wkm_h_IbVrVXM/edit#gid=0

function MovePastDates() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var entrySheet = ss.getSheetByName("Entry Sheet");
  var franksSheet = ss.getSheetByName("Franks Sheet");  
  var lastColumn = entrySheet.getLastColumn();

  for(var i = entrySheet.getLastRow(); i > 0; i--){

      var dateCell = entrySheet.getRange(i, 1).getValue();
      var today = new Date();
      var test = new Date(dateCell);


      // If the value is a valid date and is a past date, we remove it from the sheet to paste on the other sheet
      if(test < today){

        var rangeToMove = entrySheet.getRange(i, 1, 1, entrySheet.getLastColumn()).getValues();
        franksSheet.getRange(franksSheet.getLastRow() + 1, 1, 1, entrySheet.getLastColumn()).setValues(rangeToMove);
        entrySheet.deleteRow(i);

      }
   }       
}

The final result should be a google sheet that receives form entries.

Each entry will be allocated to a specific person who will only have edit permissions to there own tab only where they can approve/decline requests submitted through the form.

All other users of the sheet will have view only access.

Answer 1

I wanted two scripts:

1) Script to move form submission rows to a specific tab dependent on person’s name (I was going to set up a trigger every minute for this)

2) Script to move past dates into an historic sheet (I was going to set up a trigger every night for this)

I have been able to modify your code to achieve the desired function, it may not be the most efficient but it appears to work well.

Script One is:

   function moveRowsToNamesSheets() { //Name of function
  var sObj={John:'Johns Sheet',Frank:'Franks Sheet',David:'Davids Sheet'};   // Put key work and sheet name here in format eg.( keyWord1: 'sheet name to move keyWord1 to') 
  var ss=SpreadsheetApp.getActive();                                         // ??
  var esh=ss.getSheetByName('Entry Sheet');                                  //Sheet data is being pulled form
  var fsh=ss.getSheetByName('Franks Sheet');                                 //unsure why one of the sheets is named here
  var erg=esh.getDataRange();                                                // Not sure of function now that I am not using dates
  var evA=erg.getValues();                                                   // ??
  var d=0;                                                                   //??
  //var today=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()).valueOf();  // Didnt need this line
  for(var i=1;i<evA.length;i++) {                                            //??
    if(/*new Date(evA[i][0]).valueOf() < today*/ evA[i][2]=='John' ||evA[i][2]=='Frank' ||evA[i][2]=='David') { //Keywords used go here, what does the [2] mean?
      ss.getSheetByName(sObj[evA[i][2]]).appendRow(evA[i]);                  //??
      esh.deleteRow(i+1-d);
      d++;                                                                   //increments d by one
    }
  }
}

Script Two is:

function HistoricDates() {

  // Initialising
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //--------------- Franks Sheets --------------------
  var franksSheet = ss.getSheetByName("Franks Sheet");
  var PastSheet = ss.getSheetByName("Historic Requests");  
  var lastColumn = franksSheet.getLastColumn();

  // Check all values from your "Franks Sheet" sheet
  for(var i = franksSheet.getLastRow(); i > 0; i--){

    // Check if the value is a valid date
    var dateCell = franksSheet.getRange(i, 4).getValue();  //Dates in column 4
    if(isValidDate(dateCell)){
      var today = new Date();
      var test = new Date(dateCell);

      // If the value is a valid date and is a past date, we remove it from the sheet to paste on the other sheet
      if(test < today){

        var rangeToMove = franksSheet.getRange(i, 1, 1, franksSheet.getLastColumn()).getValues();
        PastSheet.getRange(PastSheet.getLastRow() + 1, 1, 1, franksSheet.getLastColumn()).setValues(rangeToMove);
        franksSheet.deleteRow(i);

      }
    }
  }

  //---------------------- Johns Sheets -------------------------
  var johnsSheet = ss.getSheetByName("Johns Sheet");
  var pastSheet = ss.getSheetByName("Historic Requests");  
  var lastColumn = johnsSheet.getLastColumn();

  // Check all values from your "Johns Sheet" sheet
  for(var i = johnsSheet.getLastRow(); i > 0; i--){

    // Check if the value is a valid date
    var dateCell = johnsSheet.getRange(i, 4).getValue(); //Dates in column 4
    if(isValidDate(dateCell)){
      var today = new Date();
      var test = new Date(dateCell);

      // If the value is a valid date and is a past date, we remove it from the sheet to paste on the other sheet
      if(test < today){

        var rangeToMove = johnsSheet.getRange(i, 1, 1, johnsSheet.getLastColumn()).getValues();
        pastSheet.getRange(pastSheet.getLastRow() + 1, 1, 1, johnsSheet.getLastColumn()).setValues(rangeToMove);
        johnsSheet.deleteRow(i);

      }
    }
  }  

    //--------------- Davids Sheets --------------------
  var davidsSheet = ss.getSheetByName("Davids Sheet");
  var pastSheet = ss.getSheetByName("Historic Requests");  
  var lastColumn = davidsSheet.getLastColumn();

  // Check all values from your "Davids Sheet" sheet
  for(var i = davidsSheet.getLastRow(); i > 0; i--){

    // Check if the value is a valid date
    var dateCell = davidsSheet.getRange(i, 4).getValue();//Dates in column 4
    if(isValidDate(dateCell)){
      var today = new Date();
      var test = new Date(dateCell);

      // If the value is a valid date and is a past date, we remove it from the sheet to paste on the other sheet
      if(test < today){

        var rangeToMove = davidsSheet.getRange(i, 1, 1, davidsSheet.getLastColumn()).getValues();
        pastSheet.getRange(pastSheet.getLastRow() + 1, 1, 1, davidsSheet.getLastColumn()).setValues(rangeToMove);
        davidsSheet.deleteRow(i);

      }
    }
  }

}

// Check is a valid date
function isValidDate(value) {
  var dateWrapper = new Date(value);
  return !isNaN(dateWrapper.getDate());
}

The working spreadsheet is located here: https://docs.google.com/spreadsheets/d/1VCONRkBpkva-KrFDO2bFV8ZTp1U168QWAGavcKCa_uQ/edit?usp=sharing

Answer 2

I think this is what you want:

function movePastDatesOrJohn() {
  var sObj={John:'Johns Sheet',Frank:'Franks Sheet',David:'Davids Sheet'};
  var ss=SpreadsheetApp.getActive();
  var esh=ss.getSheetByName('Entry Sheet');
  var fsh=ss.getSheetByName('Franks Sheet');
  var erg=esh.getDataRange();
  var evA=erg.getValues();
  var d=0;
  var today=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()).valueOf();
  for(var i=1;i<evA.length;i++) {
    if(new Date(evA[i][0]).valueOf() < today || evA[i][2]=='John') {
      ss.getSheetByName(sObj[evA[i][2]]).appendRow(evA[i]);
      esh.deleteRow(i+1-d);
      d++;
    }
  }
}

So Franks Sheet and Davids Sheet only get the rows that are older than today. But Johns Sheet gets all of the row that are Johns and disregards the date. I think that's what you wanted.

By the way, did you know that if you more that one form attached to your spreadsheet you can tell which response sheet the formSubmit trigger is writing too, with the event object range? Using sheet name = e.range.getSheet().getName();