is there a way to copy a specific tab with all its formatting over to a specific tab of another spreadsheet?

Question

need some help here. I have a master google sheet that has several tabs. Most of them contains private information, so i have to import a tab(sourcetab) that is for public view to another spreadsheet so that i can share it on google site.

I have tried importrange but it only copy the values to a destination tab of another spreadsheet.

Problem 1: The source tab has several links, images and formatting that i would need it on the destination. Problem 2: My management will update the master source tab and would like the destination to be updated automatically as well, with all formatting and links attached. I understand that google app script might be able to help.

Therefore, will need help to advise on how to duplicate the source tab into a destination tab and get it updated when there is changes in the master tab.

[1][Tried copytab but it only creates a new tab in the source and does not update automatically when there is any editing in the master tab]

function copyTab() {
  var ss, sourceSheet, sourceData, sourceDataRange, newSheetTab;

  ss = SpreadsheetApp.getActiveSpreadsheet();//Get active spreadsheet

  sourceSheet = ss.getSheetByName("Sample: Restriction Updates");//Get the source sheet tab
  newSheetTab = ss.insertSheet("newsheet");//Create a new sheet tab

  sourceDataRange = sourceSheet.getDataRange();

  sourceDataRange.copyTo(newSheetTab.getRange(1, 1));//Copies the data from a range of 
  //cells to another range of cells. By default both the values and formatting are copied

}

[2][Tried to create into the destination but failed]

function myFunction() {
  var source = SpreadsheetApp.openById('sourceID');
  var sourceSheet = source.getSheetByName('Sample: Restriction Updates');
  var sourceRange = sourceSheet.getDataRange();
  var sourceValues = sourceRange.getValues();

  var tempSheet = source.getSheetByName('temp');
  var tempRange = tempSheet.getRange('A1');

  var destinationsheet = SpreadsheetApp.openById('DestinationID');
  var destination = source.getSheetByName('Test');

  sourceRange.copyTo(tempRange);  // paste all formats?, broken references
  tempRange.offset(0, 0, sourceValues.length, sourceValues[0].length)
  .setValues(sourceValues);  // paste all values (over broken refs)

  tempSheet.copyTo(destination);  // now copy temp sheet to another ss
}

Answer 1

If I understand your problem correctly, you want to take the data from one sheet and directly copy it over to another. You also want to have this occur on edits.

You can try using the optional parameter with .inputSheet() and specify the template you wish to copy over and see if that works:

function myFunction() {
  var source = SpreadsheetApp.openById('sourceID');
  var sourceSheet = source.getSheetByName('Sample: Restriction Updates');
  var destinationSheet = SpreadsheetApp.openById('DestinationID');
  destinationSheet.insertSheet('Test',{template: sourceSheet});

This will transfer over values as well as formatting from the sourceSheet over into a new sheet in the destinationSheet.

As for automating this process, you can either use the .onEdit(e) trigger to run this process whenever a value is changed in the spreadsheet, or you can set up a trigger to run it on a specific basis. You can use the Apps Script Ui to set a trigger, or you can write the script and run it. Links below:

Reference:

• Simple Triggers
• Building Triggers
• insertSheet(options)

Answer 2

Your copyTab function should work, to run it on any change on a sheet, create an onEdit trigger:

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet();
  if (editedSheet.getName() === "Sample: Restriction Updates")
  {
    copyTab();
  }
}

Note: if your sheet is edited heavily (few edits per second) you might want to use Lock service to improve performance of a trigger, so your copyTab function won't be running while another instance is not finished:

function copyTab() {

  // paste at the beginning of copyTab function:
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(30000); // wait 30 seconds for others' use of the code section and lock to stop and then proceed
  } catch (e) {
    Logger.log('Could not obtain lock after 30 seconds, exiting function call.');
    return;
  }

  // rest of your function code
  ..

  // IMPORTANT release a lock in the end of function
  lock.releaseLock();
}