Appscript that creates a new worksheet when new data is entered in Google sheet-CodePudding

I have an app script code that Creates a new workbook when new data is entered in Google Sheet. The code is below.

function myFunction() {
 // The code below creates a new spreadsheet "New Sheet Name" and logs the URL for it
var ssNew = SpreadsheetApp.create();
Logger.log(ssNew.getUrl());
}

I want the new spreadsheet name to be the last data entered in Column A and also the URL for the new workbook to be pasted in Column J of the same row as the last data in Column A

CodePudding user response：

From I have an app script code that Creates a new workbook when new data is entered in Google Sheet., I thought that you might be running the script with the installable OnEdit trigger. But from your showing script, I thought that you might have wanted to directly run with the script editor. If my understanding is correct, how about the following modification?

Modified script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var title = sheet.getRange("A"   lastRow).getValue();
  var ssNew = SpreadsheetApp.create(title);
  sheet.getRange("J"   lastRow).setValue(ssNew.getUrl());
}

When this script is run, the title is retrieved from the column "A" of the last row. And, the URL of created Spreadsheet is put in the column "J" of the same row.

Reference:

getLastRow()

CodePudding user response：

Creating a new Spreadsheet

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const nss = SpreadsheetApp.create(sh.getRange(sh.getLastRow(), 1).getDisplayValue())
  sh.getRange(sh.getLastRow(), 10).setValue(nss.getUrl());
}