Creating a copy of template and pasting data from last row into it

[Jenny Lo]

Hi, 

I've posted previously but I didn't get a reply- I've realised perhaps having a copy of my doc would help please see link below:

https://docs.google.com/spreadsheets/d/1bW01-4l9odsiu6YZlu1Kj7hk1d-gWp8uwRKwXRARUtY/edit#gid=0

Below is what i've posted previously:

I'm quite new to Google script, I've been finding it interesting. I've got a sample logsheet that needs a bit of automation and I have put together a script from looking at various scripts online:

function onEdit(e) {

  var sheets = "Analysis Log";

  if (sheets.indexOf(e.source.getActiveSheet().getName()) === -1 || e.range.getColumn() !== 4) return;

  var dateObj = new Date();

  var month = dateObj.getUTCMonth() + 1; //months from 1-12

  var day = dateObj.getUTCDate();

  var year = dateObj.getUTCFullYear();

  var newdate = year + "/" + month + "/" + day;

  e.range.offset(0, -2).setValue(newdate).setNumberFormat("dd/MM/yyyy");

  e.range.offset(0, -1).setValue(new Date()).setNumberFormat("HH:mm");

}

function Createnewsampleresultsheet() {

let ss = SpreadsheetApp.getActiveSpreadsheet();

let sheet = ss.getSheetByName('Analysis Log');

let lastRow = sheet.getLastRow();

console.log(lastRow);

for (let i = 0; i <= lastRow; i++) {

let Sample_name = sheet.getRange(8+i,5).getValue();

let Sample_id = sheet.getRange(8+i, 1).getValue();

let Sample_type = sheet.getRange(8+i, 4).getValue();

let Date = sheet.getRange(8+i, 2).getValue();

let Time= sheet.getRange(8+i, 3).getValue();

console.log(i + ": " + Sample_name + " | " + Sample_id);

//Setting titles values for result sheet

let result_sheet = Sample_name + "-" + Sample_id

//Create result sheet

console.log("Sample result sheet ", "Sample_name");

let destination_folder =

DriveApp.getFolderById('1mErM-8b9n-H1BXrJXrRX19F6GPVMbSce');

let new_samplesheet =

DriveApp.getFileById('1ZDy5K4l1qhQXbYsRKNBQyscU_qX_YDEMa216TaBp6iM').makeCopy(result_sheet, destination_folder).getId();

console.log(new_samplesheet);

//Get new sample sheet

ss = SpreadsheetApp.openById(new_samplesheet);

let sampleresult = ss.getSheetByName("Sample results template");

// Sample information

sampleresult.getRange("C2").setValue(Sample_id);

sampleresult.getRange("C3").setValue(Sample_name);

sampleresult.getRange("C4").setValue(Sample_type);

sampleresult.getRange("C5").setValue(Date);

sampleresult.getRange("C6").setValue(Time);

}

}

The sample log currently has col A (starting from row 8) filled with sample IDs starting from 001. What I need for the function createnewsampleresultsheet to do is do the script whenever column E is filled out- someone edit a cell in column E, it them creates the analysis sheet. As it stands now it creates all of the sheets for all of the rows in one go- it also extends to the empty rows because the Sample IDs extends all the way down the column. 

Another thing I've been trying to do is to have the script insert the link of the newly created analysis sheet back into the corresponding row in Column A. Ie the text 001 becomes a clickable link. I think it involves getrichvalue but I could be wrong.

I'd appreciate any help on this! Definitely enjoying learning a new skill.

[Ed Sambuco]

You should be running the function only if you actually edited the value in column E, correct?

Try this:

function onEdit(e) {
  var sheets = "Analysis Log";

  if (sheets.indexOf(e.source.getActiveSheet().getName()) === -1) return;
  switch (e.range.getColumn() {
    case 4 :

       var dateObj = new Date();
       var month = dateObj.getUTCMonth() + 1; //months from 1-12
       var day = dateObj.getUTCDate();
       var year = dateObj.getUTCFullYear();
       var newdate = year + "/" + month + "/" + day;
       e.range.offset(0, -2).setValue(newdate).setNumberFormat("dd/MM/yyyy");
       e.range.offset(0, -1).setValue(new Date()).setNumberFormat("HH:mm");

       break:
    case 5 :
       Createnewsampleresultssheet();
       break;

  } 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/71d716dc-72e6-4203-9935-b88aebd9247en%40googlegroups.com.

[Jenny Lo]

Apologies, I'm not clear enough. The first script onEdit works fine as it creates a time stamp. It's the 2nd script I'm wanting help with-

We generally start entering with the name of the sample, then fill out type of sample- this causes onEdit function to happen.

With the 2nd script, createnewsampleresultsheet I am trying to achieve two things- for it to execute when a row is filled out- at the moment it is doing a loop based on what has already been entered and it's also not efficient as it will continue creating the files as column A (sample id) is already filled from 001 to 1000(as an example).

The 2nd thing i'm wanting it to do is to insert the link of the newly created sheet back into the sample ID of the corresponding row- ie 001 text becomes a clickable link.

[Ed Sambuco]

I THINK I see ... so you add the name in column E, then add type in column D, which triggers onEdit() ... OK, but if by "filling out the row" you mean entering the type with the name already there, then you still should put the call to the new results sheet within the onEdit.  That way, you are running the new results function only for the row that you just added type into.  I am assuming that column E then always has a value when onEidt() is executed for column D.

function onEdit(e) {
  var sheets = "Analysis Log";
  if (sheets.indexOf(e.source.getActiveSheet().getName()) === -1) return;

  switch (e.range.getColumn()) {

    case 4 :
       var dateObj = new Date();
       var month = dateObj.getUTCMonth() + 1; //months from 1-12
       var day = dateObj.getUTCDate();
       var year = dateObj.getUTCFullYear();
       var newdate = year + "/" + month + "/" + day;
       e.range.offset(0, -2).setValue(newdate).setNumberFormat("dd/MM/yyyy");
       e.range.offset(0, -1).setValue(new Date()).setNumberFormat("HH:mm");

       Createnewsampleresultssheet();  
       break:
      } 

}

As for a link to the sheet within the spreadsheet, this cod comes from Stackoverflow:

function getSheetUrl() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getActiveSheet();
  var url = '';
  url += SS.getUrl();
  url += '#gid=';
  url += ss.getSheetId(); 
  return url;
}

The sheet URL is then put into the sheet with a getRange(...).setValue(url)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/56880eec-64c2-47f1-954c-3a8288e553dfn%40googlegroups.com.

[Ed Sambuco]

OOPS .. missed one important thing.  You do NOT want to loop in  Createnewsampleresultssheet!  Get rid of the for loop.

You will have to change the Createnewsampleresultssheet() function.  The whole thing would now look shothing like this:

function onEdit(e) {
  switch (e.range.getColumn()) {
    case 4 :

      let ss = SpreadsheetApp.getActiveSpreadsheet();
      let sheet = ss.getSheetByName('Analysis Log');

      var dateObj = new Date();
      var month = dateObj.getUTCMonth() + 1; //months from 1-12
      var day = dateObj.getUTCDate();
      var year = dateObj.getUTCFullYear();
      var newdate = year + "/" + month + "/" + day;
      e.range.offset(0, -2).setValue(newdate).setNumberFormat("dd/MM/yyyy");
      e.range.offset(0, -1).setValue(new Date()).setNumberFormat("HH:mm");

      Createnewsampleresultsheet(sheet,e.range.getRow());
      break;
  }
}

function Createnewsampleresultsheet(sheet,row) {

  let Sample_name = sheet.getRange(row,5).getValue();
  let Sample_id = sheet.getRange(row, 1).getValue();
  let Sample_type = sheet.getRange(row, 4).getValue();
  let Date = sheet.getRange(row, 2).getValue();
  let Time= sheet.getRange(row, 3).getValue();

  //Setting titles values for result sheet
  let result_sheet = Sample_name + "-" + Sample_id
  //Create result sheet

  let destination_folder =
    DriveApp.getFolderById('1mErM-8b9n-H1BXrJXrRX19F6GPVMbSce');
  let new_samplesheet =
    DriveApp.getFileById('1ZDy5K4l1qhQXbYsRKNBQyscU_qX_YDEMa216TaBp6iM').makeCopy(result_sheet, destination_folder).getId();

[Jennifer Lo]

Yes that's correct. I've just amended the code with your suggestion: 

It does the timestamp as expected but doesn't create the file as expected? But strangely enough when i try to run the onEdit function in the code editor it shows this error:- 'TypeError: Cannot read properties of undefined (reading 'range')

onEdit @ Create sample result sheet.gs:2 

Bit odd that it still does the timestamp part despite this error. 

[Jennifer Lo]

When i also try to run  Createnewsampleresultsheet in the code editor on its own i get this error 'TypeError: Cannot read properties of undefined (reading 'getRange')
Createnewsampleresultsheet    @ Create sample result sheet.gs:20'

[Ed Sambuco]

My apologies ofr a long-winded and at times meandering response to your questions.  It's been an interesting learning experience fo me.

FRIST, do not use the simple trigger onEdit().  Use an installable trigger instead (remember that you will have to have to add the trigger to your script project.)

SECOND, use the sheet copyTo function to copy the template sheet to another project.

I tested the code  below and got it to work with a copy of your spreadsheet.

function reviseSS(e) {

  switch (e.range.getColumn()) {

    case 4 : 

      let ss = SpreadsheetApp.getActiveSpreadsheet();

      let sheet = ss.getSheetByName('Analysis Log');

      var dateObj = new Date();

      var month = dateObj.getUTCMonth() + 1; //months from 1-12

      var day = dateObj.getUTCDate();

      var year = dateObj.getUTCFullYear();

      var newdate = year + "/" + month + "/" + day;

      e.range.offset(0, -2).setValue(newdate).setNumberFormat("dd/MM/yyyy");

      e.range.offset(0, -1).setValue(new Date()).setNumberFormat("HH:mm");

      Createnewsampleresultsheet(ss, sheet, e.range.getRow());

      break;

  }

}

function Createnewsampleresultsheet(ss,sheet,row) {

  let Sample_name = sheet.getRange(row,5).getValue();

  let Sample_id = sheet.getRange(row, 1).getValue();

  let Sample_type = sheet.getRange(row, 4).getValue();

  let Date = sheet.getRange(row, 2).getValue();

  let Time= sheet.getRange(row, 3).getValue();

  Logger.log(Sample_name);

  var templateSheet = ss.getSheetByName("Sample results template"); 

  var destination = SpreadsheetApp.create("Spinoff");

  templateSheet.copyTo(destination);

  destination.getRange("C2").setValue(Sample_id);

  destination.getRange("C3").setValue(Sample_name);

  destination.getRange("C4").setValue(Sample_type);

  destination.getRange("C5").setValue(Date);

  destination.getRange("C6").setValue(Time);

}

 

Legal Disclaimer on behalf of the following companies registered in England and Wales under the following numbers (and all subsidiaries and group companies of the same):

Veolia UK Limited (2664833); Veolia Environmental Services (UK) plc (2215767); Veolia Water UK Limited (2127283); Veolia Energy UK plc (883131);

Registered office 210 Pentonville Road, London N1 9JY.

The information in this email and any associated files is confidential and may be legally privileged. It may also contain information that is subject to copyright or constitutes a trade secret. It is intended solely for the named recipient. Access to this email by anyone else is unauthorised.

If you are not the intended recipient, please note that any use,disclosure, copying, distribution of this email or any action taken or omitted to be taken in reliance on it is prohibited.

Warning: Although this email and any attachments are believed to be free from viruses, it is the responsibility of the recipient to ensure that they are virus free. No responsibility is accepted by any of the Veolia group companies for any loss or damage arising in any way from their receipt or opening.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ba0ad501-0bd1-4f6d-bb1a-b6309eb7e5ccn%40googlegroups.com.

[Jenny Lo]

Ah I forgot to state that the template file won't actually be in the same spreadsheet as the sample log, which my orginal code shows this. I've attached a crude pic of how I plan to organise the file directory. But I can just swapped back in that part into your code. I'll test it when I get onto a pc tomorrow. 

So with that in mind, how would you achieve the 2nd aim, changing the text in Column A into a URL link? I imagine it'd also have to be a part of the reviseSS(e) function...create a new function for the URL part and then call it in reviseSS?

Appreciate the help! 

[Ed Sambuco]

OK Jenny final version of the Createnewsampleresultsheet function attached.  It does a few interesting things, mostly housekeeping of the new spreadsheet after it is copied into.  Also there is the link code that you asked about, and as for moving new spreadsheet copied from template to a separate folder, I put in some simple folder logic, assuming you have unique folker names ...

function Createnewsampleresultsheet(ss,sheet,row) {

  let Sample_name = sheet.getRange(row,5).getValue();
  let Sample_id = sheet.getRange(row, 1).getValue();
  let Sample_type = sheet.getRange(row, 4).getValue();
  let Date = sheet.getRange(row, 2).getValue();
  let Time= sheet.getRange(row, 3).getValue();
  Logger.log(Sample_name);

  var templateSheet = ss.getSheetByName("Sample results template");

  var destination = SpreadsheetApp.create(Sample_name + "-" + Sample_id);
  templateSheet.copyTo(destination);
  let newSheet  = destination.getSheetByName("Copy of Sample results template");

  newSheet.getRange("C2").setValue(Sample_id);
  newSheet.getRange("C3").setValue(Sample_name);
  newSheet.getRange("C4").setValue(Sample_type);
  newSheet.getRange("C5").setValue(Date);
  newSheet.getRange("C6").setValue(Time);

 
  var url = destination.getUrl() +  '#gid=' + newSheet.getSheetId();
  sheet.getRange(row,1).setValue(url);
  let delSheet = destination.getSheetByName("Sheet1");
  destination.deleteSheet(delSheet);
  destination.renameActiveSheet(Sample_name + "-" + Sample_id);
  let destFolder = DriveApp.getFoldersByName('put folder name here').next();  
  DriveApp.getFileById(destination.getId()).moveTo(destFolder);    

 }

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4bcb81b2-6f3e-4730-96e5-72388bc26eecn%40googlegroups.com.