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

Reputation: 1127

How to use google apps script to duplicate a new google sheet while preserving the conditional formatting?

enter image description here

Hi everyone,

I have a google sheet with 2 tabs inside. The data are all come from another google sheet by using Importrange formula. I want to duplicate this google sheet by using google apps script while preserving the conditional formatting but at the same time remove the Importrange formula. In other words, there should be no Importrange formula in the duplicated sheet but still have all the formatting and data. Is there any way I can achieve my goal? There are some similar questions on internet but did not perform exactly what I want. Any help will be greatly appreciated!

Upvotes: 1

Views: 171

Answers (1)

Marios
Marios

Reputation: 27380

Explanation:

The logic is the following:

  • Duplicate the sheets in the original spreadsheet file so the conditional formatting is maintained and copy paste the values so the importrange disappears.
  • Create a new spreadsheet file and put it in the same folder as the original one and copy the sheets of the original spreadsheet file to the newly created one.
  • Delete the duplicate sheets in the original spreadsheet file.
  • Update (the name of the new spreadsheet file is given by the cell E2 of the original spreadsheet (1st sheet).

Solution:

function duplicateSpreadsheet() {
  const ss = SpreadsheetApp.getActive();
  const ssid = ss.getId();
  const folderId = DriveApp.getFileById(ssid).getParents().next().getId();
  const title = ss.getSheetByName("Data1").getRange("E2").getDisplayValue(); //added
  const new_ss = SpreadsheetApp.create(title);
  const dest_folder = DriveApp.getFolderById(folderId);
  DriveApp.getFileById(new_ss.getId()).moveTo(dest_folder);
  const sheets = ss.getSheets();
  sheets.forEach(sh=>{
      let shDup = sh.copyTo(ss);
      shDup.getDataRange().setValues(shDup.getDataRange().getValues());
      shDup.copyTo(new_ss).setName(sh.getName());
      ss.deleteSheet(shDup);
  });
    new_ss.deleteSheet(new_ss.getSheetByName("Sheet1"));
}

Upvotes: 2

Related Questions