0

I am creating a CRM for my small business. Would like that each time I add a company name it creates a new tab/sheet in the same spreadsheet for that company follow-ups and notes. Is this possible how?

Sheet to occur on: CRM Column to take names for tabs from: E1:E (Company)

1

I hope this will work Correctly

function onEdit(e) {
  var range = e.range;  //gets cell to edit
  var companyName = range.getValue();  //gets value of cell
  var column = range.getColumn();  //gets the column number of the cell
  var thisSheetNumber = range.getSheet().getIndex();

  //make sure this is the correct column on the correct sheet
  if (column == 5 && thisSheetNumber == 1)                       
  {
    //searches for a sheet that matches the companyName
    var sheetNotFound = true;    
    var sheets = SpreadsheetApp.getActive().getSheets();

    for (var i = 0; i<sheets.length; i++)                     
    {
      //if it finds matching sheet stops
      if (sheets[i].getName() == companyName)                                 
      {
        sheetNotFound = false;
        break;
      }
    }
    //else creates the sheet
    if (sheetNotFound)  
    {
      SpreadsheetApp.getActive().insertSheet(companyName);
    }
  }
}
0

This script will do it. The trigger is contained in the function name.

function onEdit(e) {
  var range = e.range;  //gets cell to edit
  var companyName = range.getValue();  //gets value of cell
  var column = range.getColumn();  //gets the column number of the cell
  var thisSheetNumber = range.getSheet().getIndex();

  //make sure this is the correct column on the correct sheet
  if (column == 5 && thisSheetNumber == 1){
     //searches for a sheet that matches the companyName
     var sheetNotFound = true;    
     var sheets = SpreadsheetApp.getActive().getSheets();

     for (var i = 0; i<sheets.length; i++){
        //if it finds matching sheet stops
        if (sheets[i].getName() == companyName){
           sheetNotFound = false;
           break;
          }
       }
//else creates the sheet
if (sheetNotFound){
  SpreadsheetApp.getActive().insertSheet(companyName);
    }
  }
}
  • I think that using a reserved word (onEdit) for a function that will be called by a trigger is not a good practice b/c this leads to confusions and could make it harder to debug / maintain. By the other hand, please add a explanation of what your code does and how it works. – Rubén Feb 5 '17 at 15:59
  • Added comments. Yeah, the trigger and the name were redundant as using the reserved name meant I didn't need set up a separate trigger that did the same thing. – Hink Feb 5 '17 at 16:36
  • Bear in mind that functions called by simple triggers can't do all the things than functions called by installable triggers. Did you tested your script by calling it by a simple trigger instead of using an installable trigger? – Rubén Feb 5 '17 at 17:13
  • It worked as the scope is limited to the document. – Hink Feb 5 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.