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

Question

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

score 2 · Answer 1 · answered May 24 '22 at 01:29

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()

score 1 · Accepted Answer · answered May 24 '22 at 01:41

1

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());
}

answered May 24 '22 at 01:41

Cooper

59,616
6
23
54

thanks a lot. it worked! I have one more favor to ask of you. Can you edit the script so it would add the same people in the first Worksheet to the newly created worksheets? – Damilola Ogundun May 24 '22 at 02:24
Yes. Please describe the content in ss that you wish copied. – Cooper May 24 '22 at 02:54
I'm a new apps script user, I don't understand this – Damilola Ogundun May 24 '22 at 03:05
Do just wish to copy some sheets from one spreadsheet to another?If so which ones if no then what? – Cooper May 24 '22 at 03:17
I meant that everything you provided in the last code worked perfectly. I just want to add a function to it that shares the newly created worksheets with the same people the main sheet was shared to – Damilola Ogundun May 24 '22 at 03:24
Explain which worksheets – Cooper May 24 '22 at 03:26
the ones created by the apps script that is being triggered by OnEdit Trigger – Damilola Ogundun May 24 '22 at 03:38
There is no onEdit described in this question. – Cooper May 24 '22 at 04:09
yes, I created the onEdit trigger manually – Damilola Ogundun May 24 '22 at 22:14
Yes but the onedit function is not posted in this question so we don't know anything about it – Cooper May 24 '22 at 22:59
I have rephrased the question here. https://stackoverflow.com/questions/72370305/apps-script-that-shares-a-google-sheet-to-a-specific-group-of-people please do check it out – Damilola Ogundun May 24 '22 at 23:03
https://stackoverflow.com/q/72370305/18872230 – Damilola Ogundun May 24 '22 at 23:09

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

2 Answers2

Modified script:

Reference:

Creating a new Spreadsheet