Google Apps Script - copy to existing spreadsheet

Question

I'm attempting to use Google Apps Script copy() to 'publish' my master spreadsheet to an output spreadsheet, but getting multiple copies each time instead of it replacing the output file. Can anyone suggest a way to replace the contents of a destination spreadsheet so I can keep the same file-ID for the output and manually trigger a 'publish'. Have tried copyTo, but just makes multiple sheets instead.

The master spreadsheet is a staff roster that needs to be able to be worked on by multiple managers without staff seeing the live version. When the manager has finished updating, it can be pushed to staff.

Edit: Got it working

function publishRoster() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getActiveSheet();
  var updatedDateTime = sheet.getRange("A1");
  var now =  Utilities.formatDate(new Date(), "GMT+10:30", "dd/MM/yyyy H:mm")
 updatedDateTime.setValue("Last Published " + now);
  var sourceName = source.getSheetName();
//  var sValues = source.getDataRange().getValues();
  var destination = SpreadsheetApp.openById('my-destination-sheet-id-here');
  var destinationSheet = destination.getSheetByName(sourceName);
  if (destinationSheet == null ) { }
 else { destination.deleteSheet(destinationSheet); }
  sheet.copyTo(destination).setName(sourceName);
}

Umair Mohammad Umair Mohammad · Accepted Answer · 2017-12-14T07:59:03

copyTo() creates a copy of existing sheet in the destination spreadsheet. If you want to publish the changes from a specific sheet to specified destination sheet then you can copy the data from source sheet to destination sheet, instead of copying the whole sheet.[which will of course create new sheets each time you copy]

So the code to copy/publish data from master sheet to slave sheet goes as follows :

var SOURCEID = 'xxxxxxxxxxxxx'; //put your source spreadsheet id here
    var SOURCESHEETNAME = 'XXXXX'; //put your source sheet name here
    var DESTINATIONID = 'xxxxxxxxxxxxx'; //put your destination spreadsheet id here
    var DESTINATIONSHEETNAME = 'XXXXX'; //put your destination sheet name here

    var data = SpreadsheetApp.openById(SOURCEID).getSheetByName(SOURCESHEETNAME).getDataRange().getValues();
    SpreadsheetApp.openById(DESTINATIONID).getSheetByName(DESTINATIONSHEETNAME).clear(); //This line is to clear the existing data in destination.
    SpreadsheetApp.openById(DESTINATIONID).getSheetByName(DESTINATIONSHEETNAME).getRange(1, 1, data.length; data[0].length).setValues(data);
    //data.length = no. of rows in source
    //data[0].length = no. of columns in source
    var now =  Utilities.formatDate(new Date(), "GMT+10:30", "dd/MM/yyyy H:mm");      
SpreadsheetApp.openById(DESTINATIONID).getSheetByName(DESTINATIONSHEETNAME).getRange("A1").setValue("Last Published " + now);

This is not a tested code, let me know if any issues arises, I'll be happy to help you. Thanks

Google Apps Script - copy to existing spreadsheet

1 Answers