Google-sheets – How to use 3 separate sheet specific onEdit formulas

google sheetsgoogle-apps-script

So I have 3 separate sheets in on Google Sheet Doc, I have a script running on the first sheet that hides a row when a drop down is ammended:

var SHEET = "Tweaks Needed";
var VALUE = "All Tweaks Made";
var COLUMN_NUMBER = 7

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();

if(SHEET == activeSheet.getName()){
  var cell = ss.getActiveCell()
  var cellValue = cell.getValue();

  if(cell.getColumn() == COLUMN_NUMBER){
    if(cellValue == VALUE){
      activeSheet.hideRow(cell);
   };
  };
 }; 
}

I have then generated a second script as follows:

var SHEET = "Standalone";
var VALUE = "Correct";
var COLUMN_NUMBER = 5

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  if(SHEET == activeSheet.getName()){
    var cell = ss.getActiveCell()
    var cellValue = cell.getValue();

    if(cell.getColumn() == COLUMN_NUMBER){
      if(cellValue == VALUE){
        activeSheet.hideRow(cell);
      };
    };
  };
}

When I add this second script, it completely overrides the first one, causing it to not function. I do have a third script however this is the same concept.

Is there a way to combine these two scripts so that both work accordingly?

Best Answer

onEdit scripts are per google sheet documents, not per individual tab/spreadsheet. You should change your flow so that it follows something like this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  if("Standalone" == activeSheet.getName()){
    // logic for sheet Standalone       
  } else if ("Tweaks Needed" == activeSheet.getName()) {
    // logic for sheet Teaks Needed
  }
};

Related Solutions

Google-sheets – Fetch cell row and column indices in Google Sheets

With the following little piece of code you can accomplish that.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Related Topic