Appscript to create a new worksheet when new data is entered in Google sheet-CodePudding

I'm a total newbie at this and your help will be appreciated. I need someone to help me out with an app script code that creates a new worksheet in Google Drive each time a new data is entered in Column A of a Google Sheet (lets call it master sheet). The name of the new worksheet would be the last data entered in Column A of Master Sheet. The link of the New sheet will also be inserted into column J of Master Sheet

CodePudding user response：

Try this:

function colAEdit(e){
  var sh = e.range.getSheet();
  var aLast = sh.getRange("A" (sh.getLastRow() 1)).getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); //get the last row
  
  // The if statement below will check the following: if the edited sheet is Master Sheet,  
  // if the edited cell row is > 1, if the edited cell row is the latest row  
  // if the edited cell col is == 1, if old value of edited cell is null and if the edited cell has content
  if(sh.getName() == "Master Sheet" && e.range.getRow() > 1 && 
      e.range.getRow() == aLast && e.range.getColumn() == 1 && 
      !e.oldValue && e.value){                                
        var newSpreadSheet = SpreadsheetApp.create(e.value);
        if(newSpreadSheet){
          e.range.offset(0, 9).setValue(newSpreadSheet.getUrl())
        }
      }
}

Make sure to create an Installable Trigger in your Apps Script.

To set up your Trigger, In your Apps Script Editor, Hover your mouse to the left menu, Click Triggers, Click Add Trigger and copy the setup here:

Output:

Demo:

References:

CodePudding user response：

Insert a sheet on edit of ColumnA

Name Sheet with contents of column A and insert sheet link in column J

function onEdit(e) {
  //e.source.toast('Entry1')
  const sh = e.range.getSheet();
  if (sh.getName() == "Master Sheet" && e.range.columnStart == 1 && e.range.rowStart > 1 && !e.oldValue && e.value) {
    //e.source.toast('Flag1');
    let sh = e.source.insertSheet(e.value);
    if (sh) {
      e.range.offset(0, 9).setValue(`https://docs.google.com/spreadsheets/d/${e.source.getId()}/edit#gid=${sh.getSheetId()}`)
    }
  }
}

For this version you will probably need to use an installable onEdit trigger which should not be named onEdit.

function onMyEdit(e) {
  //e.source.toast('Entry1')
  const sh = e.range.getSheet();
  if (sh.getName() == "Master Sheet" && e.range.columnStart == 1 && e.range.rowStart > 1 && !e.oldValue && e.value) {
    //e.source.toast('Flag1');
    let nss = SpreadsheetApp.create(e.value);
    if (nss) {
      e.range.offset(0, 9).setValue(`https://docs.google.com/spreadsheets/d/${e.source.getId()}/edit#gid=${sh.getSheetId()}`)
    }
  }
}