Auto populate multiple identical sheets with data from one sheet

Question

Using Google apps script I am trying to auto populate multiple sheets with data from one sheet.

I am using two Spreadsheets, the "master" spreadsheet contains the data that needs to be auto populated into the other spreadsheet.

The other spreadsheet contains the template sheet which is duplicated multiple times depending on the number of entries in the "master" sheet, this is where the data needs to go.

So far I have managed to duplicate the number of template sheets based off the master and delete them afterwards (see code), but I can't find anything about how I can now auto populate these sheets. The template is duplicated depending on the number of companies in a column, the template sheets are named after these companies.

function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [{name: "Produce Conformance Logs (From Current Sheet)", functionName: "Produce"}, 
{name: "Delete Old Conformance Logs", functionName: "delSheets"}
];
ss.addMenu("Conformance Logs", menuEntries);
}




function Produce() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var ui = SpreadsheetApp.getUi();

var eRange = sh.getRange("R9:R").getValues()
var eLength = eRange.filter(String).length;


var rData = sh.getRange(9, 18,eLength, 1).getValues(); // This is the data with the company names

var conformanceSSheet = SpreadsheetApp.openById("1eMQv7muCHzKiyFxIlsdfgsdfgNpxovHiEINNZNtPUlEElIFs5ws");
var conformanceTemplate = conformanceSSheet.getSheetByName("Template");

var result = ui.alert(             //title, prompt, buttons)  
'Please confirm','Are you sure you want to produce Conformance Logs ?', ui.ButtonSet.YES_NO); 

if(result == ui.Button.YES){

for (i=rData.length-1;i>0;--i)
conformanceTemplate.copyTo(conformanceSSheet).setName(rData[i]);
}

}

Auto populate code to go here

function delSheets() {

var ss =  SpreadsheetApp.openById("1eMQv7muCHzKiyFxIlNpxovHiEsdfgsdgINNZNtPUlEElIFs5ws");
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();
var ui = SpreadsheetApp.getUi();

var deleteSheetsContaining = "Template"
for (var i = 0; i < sheetsCount; i++){
var sheet = sheets[i]; 
var sheetName = sheet.getName();
Logger.log(sheetName);

if (sheetName.indexOf(deleteSheetsContaining.toString()) === -1){
Logger.log("DELETE!");
ss.deleteSheet(sheet);
}
}
}

For example in cell F7 of all the duplicated sheets I am trying to populate the company names which I have identified earlier in the rData variable. Is it possible to populate one company name per sheet ?

Thanks for any help !

Steve Rajesh Mohanan Steve Rajesh Mohanan · Accepted Answer · 2016-02-02T11:51:24

//try to see if you can edit this to make it work for u;

    function archive()
    {var sheetNameToWatch = "XXXXX";
    var sheetNameToMoveTheRowTo = "YYYYYY";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[10];
    var range = sheet.getRange("C4:K5");
    {
     var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
     var targetRange = targetSheet.getRange(targetSheet.getLastRow() +1,1);
     sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange,{contentsOnly:true});}

Auto populate multiple identical sheets with data from one sheet

1 Answers