Create a new document using Google Script using multiple values as document name

Question

I'm creating a template to create test cases in Google Drive. Using Google Documentation and Stackoverflow I created the code below to save the template in a new document.

// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Save Test Case", functionName: "saveTestCase"});
  ss.addMenu("Test Case Menu", menuEntries);
}

// The code below makes a duplicate of the active sheet
function saveTestCase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var name = sheet.getRange("Test Case Template!B3:G3").getValue();
  var destFolder = DriveApp.getFolderById("xxxxxx");
  DriveApp.getFileById(sheet.getId()).makeCopy(name, destFolder);
}

I created a custom menu item with a 'save' of the template. The save creates a new document in a set folder. All good so far. But in the same action I try to rename the new document. Therefore I set a range, because the name must be get over multiple fields. And that part does not work. Instead of getting the values of B3:G3, it only gets the value of B3.

Anyone an idea if it is possible and how I can fix it?

Most ideal for me would be that the value of B3:G3 in this picture turns into the document name "TC-001 This is a test case".

EDIT:

With the solution of Amit, the code for the name looks like this:

// The code below makes a duplicate of the active sheet
function saveTestCase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var name = sheet.getRange("Test Case Template!B3:G3").getValues().join(" ");
  var destFolder = DriveApp.getFolderById("xxxxxx");
  DriveApp.getFileById(sheet.getId()).makeCopy(name, destFolder);
}

It indeed fixes my first problem. The outcome however is a name separated with commas. Therefore I changed it into this for now:

// The code below makes a duplicate of the active sheet
function saveTestCase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var nameA = sheet.getRange("Test Case Template!B3").getValue();
  var nameB = sheet.getRange("Test Case Template!C3").getValue();
  var nameC = sheet.getRange("Test Case Template!D3").getValue();
  var nameD = sheet.getRange("Test Case Template!G3").getValue();
  var name = nameA + nameB + nameC + ' ' + '//' + ' ' + nameD;
  var destFolder = DriveApp.getFolderById("1pXJvplBAMz8oeS5tkyWVZSUCJsTuNrcS");
  DriveApp.getFileById(sheet.getId()).makeCopy(name, destFolder);
}

Which results in the perfect file name including enters and all the data I need in the file name.

Any recommendations for improvement are always appreciated.

Amit Agarwal Amit Agarwal · Accepted Answer · 2018-12-20T09:18:11

You are using the getValue method that will only return the first cell value in a range. Use getValues() instead which returns an array of values that you can convert to a string with the join(delimiter) method.

Replace:

var name = sheet.getRange("Test Case Template!B3:G3").getValue();

with:

var name = sheet.getRange("Test Case Template!B3:G3").getValues().join(" ");

Create a new document using Google Script using multiple values as document name

1 Answers