How do I paste contents of one worksheet onto a new worksheet that gets its name from a specific cell in Google Scripts?

user_16761112 0
Viewed 52

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);
}
google-apps-script google-sheets
2 Answers

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:

user_71086530

Copy Sheet to Another Spreadsheet

This actually works but could be greatly improved with a custom dialog. I'm working on that right now. It does allow one to chose between multiple files with the same name. The dialog displays the name of the file and path for each file it finds allowing the user to select which one they wish to copy the local sheet to.

function getSpreadsheetByName() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const sh = ss.getActiveSheet();
  const tssname = sh.getRange("C1").getValue();
  const files = DriveApp.getFilesByName(tssname);
  if (files.hasNext()) {
    while (files.hasNext()) {
      let file = files.next();
      let path = getPath(file.getId());
      let r = ui.prompt(`Name: ${file.getName()}`, `Path: ${path}`, ui.ButtonSet.OK_CANCEL);
      if (r.getSelectedButton() == ui.Button.OK) {
        sh.copyTo(SpreadsheetApp.openById(file.getId()));
        return;
      } else {
        continue;
      }
    }
  } else {
    ui.alert('No files found');
  }

}

Appearance of current dialog:

enter image description here

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;
}
user_72150910
Related