CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
piyush
piyush

Reputation: 621

Using google drive api to duplicate a worksheet

I am tring to copy/duplicate a worksheet using google apps script. Is that possible to do so ? I am not able to find any reference of such a call in API docs. https://developers.google.com/google-apps/spreadsheets/

Upvotes: 1

Views: 1769

Answers (2)

vipers36
vipers36

Reputation: 300

You can use a google apps script deployed as a web app. Call the web app using HTTP GET to transfer the spreadsheet ID, name of the worksheet to be copied and name of the new worksheet. Here is the script I use:

function doGet(e) {

  // get parameter from request (GET HTTP method)
  var spreadsheetID = e.parameter.spreadsheetID;
  var targetName = e.parameter.targetName;
  var sourceSheetName = e.parameter.sheetName;

  var response = copyWorksheet(spreadsheetID, sourceSheetName, targetName);

  return ContentService.createTextOutput(response);
}


function copyWorksheet(spreadsheetID, templateName, sheetName){

    // if there is a spreadsheet ID, find the spreadsheet with this ID
    if(typeof spreadsheetID == "string"){
      spreadsheet = SpreadsheetApp.openById(spreadsheetID);
    }else{
      return "failure; the spreadsheet " + spreadsheetID + " was not found";
    }

    // if there is a template name, find the worksheet with this name and set it as the active sheet;  (if there is no name provided, the active worksheet is duplicated)
    if(typeof templateName == "string"){
      template = spreadsheet.getSheetByName(templateName);
      if(template != null)
        spreadsheet.setActiveSheet(template);
    }

    // duplicate active sheet and set the new sheet as the active sheet
    newSheet = spreadsheet.duplicateActiveSheet();

  // rename the sheet
    if(typeof sheetName == "string"){
      sheetName.setName(sheetName);
    }

  return "success;" + sheetName + ";";
}

attach this script to any spreadsheet and deploy it. Call it using:

<scriptURL>?spreadsheetID=<spreadsheetID>&targetName=<targetName>&templateName=<sourceSheetName>

Upvotes: 1

eddyparkinson
eddyparkinson

Reputation: 3700

Sorry, as far as I know, there is no function to do that. Google apps script will do it.

I did it the manual way. I created a new sheet and did a copy of the formulas/data. But there are bugs if you use $ in your formulas, the read works fine, but the write is a pain, as some formulas, e.g. with A$1 references won't write to the sheet (because of a bug). So you need to switch them to A1. Also you can't copy fonts/colours etc.

My code for doing this is in Java, but is not easy to split out the bit you need.

Upvotes: 1

Related Questions