I have got this code that makes me a new worksheet where the name of the new worksheet is based off of the value in a worksheet named Questionnaire, "C1". After this new worksheet is made, I want to copy the contents from Questionnaire "A1:B16" and paste in this new worksheet "A1:B16". I will be doing this for lots of different new worksheet names so the final code will have to adapt to the new worksheet name each time.

function GenerateName() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = sheet.getRange("C1").getValue();

sheet.insertSheet(newSheet);
}

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the value from the cell "C1" of the sheet "Questionnaire", and want to insert new sheet with the retrieved value.
• You want to retrieve the values from "A1:B16" from the sheet "Questionnaire" to the inserted new sheet with the same range.

In this case, how about the following modified script?

Modified script:

function GenerateName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Questionnaire");
  var newSheetName = sheet.getRange("C1").getValue();
  var newSheet = ss.insertSheet(newSheetName);
  sheet.getRange("A1:B16").copyTo(newSheet.getRange("A1"), { contentsOnly: true });
}

• If you want to copy not only values but also the cell style, please modify sheet.getRange("A1:B16").copyTo(newSheet.getRange("A1"), { contentsOnly: true }); to sheet.getRange("A1:B16").copyTo(newSheet.getRange("A1"));.

References:

• 

  Helper function to get file Path:

  function getPath(id) {
    try {
      var file = DriveApp.getFileById(id)
      var pA = [];
      pA.push(file.getName());
      var folder = file.getParents();
      while (folder.hasNext()) {
        var f = folder.next();
        pA.push(f.getName());
        folder = f.getParents()
      }
      var r = pA.reverse().join(' / ');
    }
    catch (e) {
      return e;
    }
    return r;
  }