CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
CJE615
CJE615

Reputation: 11

Google Form Response Sheet script that triggers copy of a template spreadsheet to a new filename

I have created:

  1. Google Sheet as a Master Template ("QC_MasterTemplate") in my root drive that has all formatting as I want for a Checklist to be completed for every new sales order taken.

  2. Google Form to collect the new customer name, id and date order was received that our salespersons will use to submit their response.

  3. Google Sheet linked to Form storing new order Responses. I also have a my script running on Form Submit of new entry to this sheet that collects the last row entry information.

What I need help with:

The script in the Response Sheet is working to trigger my notification email that a new entry has been made and it breaks down the details of each cell to format in the message of the email just fine.

I'm trying to automate the next step that takes the new entry information to be used in formatting a new Spreadsheet filename. (ie. "companyname-customerid-orderdate-QCChecklist") This variable is easy to build, the problem I'm having is then getting the Response Sheet script to take the non-currently active spreadsheet ("QC_MasterTemplate") and make a copy in the same root location with the new filename

The code I'm using is:

function createNewSheet() {
  // Get Template Filename
  var TemplateFile = DriveApp.getFilesByName("QC_MasterTemplate");
  // Get current spreadsheet for information lookup
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Last row of data to process
  var startRow = sheet.getLastRow();
  // Starting Column of data to process
  var startColumn = 1;
  // Number of rows to process
  var numRows = 1;
  // Number of columns to process
  var numColumns = sheet.getLastColumn();
  // Get range of cells in columns A:E for all rows with entries
  var dataRange = sheet.getRange(startRow, startColumn, numRows, numColumns)
  // Get entries for each row in the Range.
  var data = dataRange.getValues();
  // Set each value to a unique variable
  for (i in data) {
    var cell = data[i];
    var timestamp = cell[0];
    var companyname = cell[1];
    var customerid = cell[2];
    var orderdate = cell[3];
    var employee = cell[4];
    // Build body of email
    var subject = companyname + "-" + customerid + "-" + orderdate + " - QC Checklist";
    var message = 'New QC Checklist Form Received \n' +
      '\nTimestamp: ' + timestamp +
      '\nCompany Name: ' + companyname +
      '\nCompany ID: ' + customerid +
      '\nOrder Date: ' + orderdate +
      '\nEmployee Name: ' + employee;
    var recipient = "[email protected]";
    MailApp.sendEmail(recipient, subject, message); 
    // Make copy of Template to new filename
    subject.makeCopy(TemplateFile);
  }
}

The error I get is: TypeError: Cannot find function makeCopy in object Striker-37188-20150401 - QC Checklist. (line 39, file "Code")

Upvotes: 1

Views: 1626

Answers (3)

nlewis59
nlewis59

Reputation: 1

Could one also use the .makeCopy(name, destination) method thusly?

var ss = SpreadsheetApp.getActiveSpreadsheet();
var lmName = (ss.getName() + "_2016-11");
var lmfoldername = ('2016-11-Update');
var lmFolder = DriveApp.getFoldersByName(lmfoldername).next(); 
DriveApp.getFileById(ss.getId()).makeCopy(lmName, lmFolder);

Upvotes: 0

Alan Wells
Alan Wells

Reputation: 31300

Your variable named subject is a string. There is no makeCopy() method to make a file from a string. Google Apps Services are object oriented programming. You need to start at the top, then work your way down to the method. There is a makeCopy() method:

Google Documentation

But you can't just use that method on anything. For that particular documentation, the makeCopy() method only works on the File Class. So you first need to get a reference to a file.

There are probably multiple ways you can make a copy of the template.

One option is to use the Class DriveApp. 

function makeCopyOfTemplate() {
  var templateID = 'The File ID of the File'; //Template File ID
  var workingCopy = DriveApp.getFileById(templateID).makeCopy();
  var workingFileID = workingCopy.getId();

  var fileNameToUse = 'CustomerABC Todays Date';
  //Set the file name of the copy file
  workingCopy.setName(fileNameToUse);

  //Open the new copy of the Template
  var workingCopyOfTemplateFile = SpreadsheetApp.openById(workingFileID);

  //To Do:  Inject copy with data
};

Upvotes: 0

KRR
KRR

Reputation: 4917

Instead you can get the parent folder for the spreadsheet(QC_MasterTemplate), go to that folder and create the new spreadsheet file.

In order to get the root folder of the spreadsheet(QC_MasterTemplate) you can use getParents() method from DriveApp.

Also for creating a spread sheet you have to use create(name) method.

Hope that helps!

Upvotes: 0

Related Questions