CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Draco D
Draco D

Reputation: 334

Dynamically edit columns in google sheets

Context

I am creating a database of stock price data. I am currently using the below onEdit function:

function onEdit(e) {
  // Get the spreadsheet that this will apply to, the tabs and columns
  var ss = e.source.getActiveSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; // the columns to isolate for special reasons
  var excludeCells = ["M1","I1","I2"];
  // What is the criteria for action to be taken? If the terms defined in excludeTabs are met, then move onto the next criteria (indexOf used because we stored it as an array
  if(excludeTabs.indexOf(ss.getName())===-1){
    // The range from the spreadsheet. 
    // Scripts expects an event in a cell
    var cell = e.range;
    // For the entire column of this cell
    var col = cell.getColumn();
    // Within the universe of tabs that this script applies to, we want to exclude the columns in the array
    if(excludeColumns.indexOf(col)===-1)
      // Within the universe of tabs and columns, we want to exclude certain cells
      if(excludeCells.indexOf(cell)===-1)
      // Need to make sure it only applies to formulas
        if(cell.getFormula() !== ""){ 
          var destination = ss.getRange(4, col, ss.getLastRow()-1, 1); 
          cell.copyTo(destination);  
      }//End of the remaining universe of data taking out the exceptions
     }//End Tab criteria
}//End function

This allows for an edit in some of the columns to be performed when I edit the cell. So far it works but with a few kinks.

Problem 1

Sometimes, when I edit a cell above the fourth row of a column, it edits the entire column despite me telling it to start from the fourth row. This happened just a few minutes ago in a cell I told it to exclude above "I2". Is there anything wrong with the code I have written to this effect?

Problem 2 I tried creating other exceptions for the code, where for some specified ranges, it will only edit from a different cell range. Not the fourth cell of every column but of say the 10th cell. I tried adding it below var destination = ss.getRange(4, col, ss.getLastRow()-1, 1) but it did not work. I also tried creating a separate onEdit function for a different cell location but it also did not work.

So far I have been using the sheet formulas like the below: IFERROR(IFS(C4="Returns","",AND(C4="",C5="",C6="",C7="",C8=""),"",AND(ISNUMBER(C4),ISNUMBER(C5),ISNUMBER(C6),ISNUMBER(C7),ISNUMBER(C8)),COVAR($C4:$C8,'FTSE 250'!J5:J9)),""))

But this just gets messy. Sometimes there is data in the cell and so it would render formulas like the above useless. An example is the below picture.

Update I want the onEdit to start and drag down from the 10th row of the column but only for that column (this is the row in that column that I will be editing). I also want to be able to do this for other columns (start the automatic copy down process from different rows).

[Range I am trying to edit (starting from the 5th day of the week)1

Update 2

...

 if(!excludeTabs.includes(ss.getName()) &&  
     !excludeColumns.includes(col) &&
     !excludeCells.includes(cell.getA1Notation()) &&
     cell.getFormula() !== ""
    ){
      
    if(col==33){
      var destination = ss.getRange(8, col, ss.getMaxRows()-7, 1);
      cell.copyTo(destination);    
    }

   else if(col===30){
          var destination = ss.getRange(8, col, ss.getMaxRows()-7, 1);
          cell.copyTo(destination); 
        }

    else{
    var destination = ss.getRange(4, col, ss.getMaxRows()-3, 1);
    cell.copyTo(destination);    
      }


    }

Upvotes: 1

Views: 235

Answers (2)

Marios
Marios

Reputation: 27350

Explanation:

Issues:

  • Your if statements don't have closed brackets with code in it.

  • Here if(excludeCells.indexOf(cell)===-1) there is a problem:

    var excludeCells = ["M1","I1","I2"]; is an array of strings and var cell = e.range; is a range object. You are actually comparing two different things (a string vs a range object.)

    Instead you want to replace: if(excludeCells.indexOf(cell)===-1) with if(excludeCells.indexOf(cell.getA1Notation())===-1).

Improvements:

  • Instead of using multiple if statements which at the end lead to one single code block, use one if statement with multiple conditions.

  • Also this range getRange(4, col, ss.getLastRow()-1, 1); does not make a lot of sense either. It makes more sense to use ss.getLastRow()-3 because you are starting from 3.

  • Instead of using excludeCells.indexOf(cell.getA1Notation())===-1 which is a long expression, you can use includes() like that !excludeCells.includes(cell.getA1Notation()).

Solution:

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; // the columns to isolate for special reasons
  var excludeCells = ["M1","I1","I2"];
  var cell = e.range;
  var col = cell.getColumn();
  
  if(!excludeTabs.includes(ss.getName()) &&  
     !excludeColumns.includes(col) &&
     !excludeCells.includes(cell.getA1Notation()) &&
     cell.getFormula() !== ""
    ){         
      if(col==33){
       var destination = ss.getRange(10, col, ss.getMaxRows()-9, 1);
       cell.copyTo(destination);    
      }
      else{
       var destination = ss.getRange(4, col, ss.getMaxRows()-3, 1);
       cell.copyTo(destination);    
      }
     }
}

Please Note:

  • getLastRow() returns the last row with content. For example if you have 10 columns and the first 10 have last row with content to be 55 but there is a random value in column 20 at the end of the sheet let's say row 900 then 900 will be the last row in your sheet. Be careful with that, otherwise you will need other approach to get the last row with content. Formulas are content too. So a formula all the way to the bottom of the sheet might determine what getLastRow returns.

Upvotes: 1

Cooper
Cooper

Reputation: 64062

Try this:

function onEdit(e) {
  var sh = e.range.getSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; 
  var excludeCells = ["M1","I1","I2"];
  if(excludeTabs.indexOf(sh.getName())==-1 && excludeColumns.indexOf(e.range.columnStart)==-1 && excludeCells.indexOf(e.range.getA1Notation())==-1 && e.range.getFormula()!=""){    
      e.range.copyTo(sh.getRange(4, e.range.columnStart, sh.getLastRow()-3, 1));//The numbers of rows to the bottom is sh.getLastRow()-3 -1 will have to roll off of the bottom of the spreadsheet which will give you out of range errors  
    }
  }
}

Upvotes: 1

Related Questions