Question

0

197

Views

App script - Create copy of single tab/sheet

Im trying to .makecopy of a single tab or sheet from a spreadsheet with multiple tabs, Im able to make a copy of the entire sheet however this includes information the end user does not need to see.

I've seen some threads about creating a new spreadsheet within apps script then coping over the detail although formatting gets messy, I know there is a way to fix this but i'd like to know if there is a method as simple as the one i use currently to copy an entire sheet.

//Written like a true noob.

function Createcopy() {
//Base
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Review')
var date = sheet.getRange(1,3).getValue(); // Probably wont need.

//Folder detail

var folderid = '1iEILmAp3JiOTiPjFbDhv0bAfgu6AWhtd'
  var Folder = "https://drive.google.com/drive/folders/"+folderid
  Logger.log(folderid)

//Create copy
var title = sheet.getRange(1,4).getValue();
Logger.log(title)
var filename = title
    var destFolder = DriveApp.getFolderById(folderid);
    var createdcopy = DriveApp.getFileById(ss.getId()).makeCopy(filename, destFolder); 

  var url = createdcopy.getUrl()
  Logger.log(url)
  
}

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-06-25T22:05:52.348Z

I believe your goal is as follows.

You want to copy the specific sheet in a Google Spreadsheet to a new Google Spreadsheet.
You want to put the new Spreadsheet in the specific folder.
You want to retrieve the URL of the new Spreadsheet.

Modification points:

As you say, your script copies the whole Spreadsheet.
In this case, I would like to propose the following flow.
1. Retrieve source sheet.
2. Copy the source sheet to a new Spreadsheet and remove the default empty sheet.
3. Move a new Spreadsheet to the destination folder.
4. Retrieve the URL of a new Spreadsheet.

When this flow is reflected in your script, it becomes as follows.

Modified script:

function Createcopy() {
  var sheetName = 'Review'; // Please set the source sheet name.
  var folderId = '###'; // Please set the destination folder ID.

  // 1. Retrieve source sheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var title = sheet.getRange(1, 4).getValue(); // New Spreadsheet title. This is from your script.

  // 2. Copy source sheet to a new Spreadsheet and remove the default empty sheet.
  var newSS = SpreadsheetApp.create(title);
  sheet.copyTo(newSS).setName(sheetName);
  newSS.deleteSheet(newSS.getSheets()[0]);

  // 3. Move a new Spreadsheet to the destination folder.
  var dstFolder = DriveApp.getFolderById(folderId);
  DriveApp.getFileById(newSS.getId()).moveTo(dstFolder);

  // 4. Retrieve URL of a new Spreadsheet.
  var url = newSS.getUrl();
  Logger.log(url)
}

When this script is run, the source sheet is copied to a new Spreadsheet. And, at a new Spreadsheet, the default empty sheet is removed. By this, a Spreadsheet including the specific sheet is obtained.

Note:

In this case, if the source sheet includes the formulas for loading the values from another sheet, an error occurs. Please be careful about this. And also, for example, when the source sheet includes the custom function, I think that the following script might be able to be used.

  function Createcopy() {
    var sheetName = 'Review'; // Please set the source sheet name.
    var folderId = '###'; // Please set the destination folder ID.

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    var title = sheet.getRange(1, 4).getValue(); // New Spreadsheet title. This is from your script.
    var dstFolder = DriveApp.getFolderById(folderId);
    var newSSFile = DriveApp.getFileById(ss.getId()).makeCopy(title, dstFolder);
    var newSS = SpreadsheetApp.open(newSSFile);
    newSS.getSheets().forEach(s => {
      if (s.getSheetName() != sheetName) newSS.deleteSheet(s);
    });
    var url = newSS.getUrl();
    Logger.log(url)
  }

0

197