Automatically copy multiple tabs data to another google sheet tabs.

This code will automatically copy google spreadsheet tabs data to another google spreadsheet tabs with just single click.

Just copy this code and paste this code in source spreadsheet script editor.

Conditions for applying this code:-

1> Code will be placed in Source spreadsheet

2> Destination spreadsheet (that needs to be updated) and target spreadsheet both should be in the same folder.(You can give any name to folder).and folder should have only these two files(do not add any third File in this folder)

3> Both spreadsheets should have same Tab names(case sensitive),E.g : If you have tab name “Classified” in report sheet but in master sheet that tab name is written “classified” then data will not update in classified tab .

Note:-This code will also create tabs with data that are not in destination spreadsheet . .

4> Plz run the code after all your work has been done. Don’t update it twice or thrice, otherwise there will be duplicate entries in the master sheet for that date.

////Create custom menu

function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu(“Update”)
.addItem(‘Master Sheets’, ‘myFunction’)
.addToUi();
}

////On Google sheet load, menu created.
function onInstall(e) {
onOpen(e);
}
function myFunction() {

//Function for automaticaly creating sheets where data needs to be pasted

sheetcreate()

//Get source spreadsheet id
var sss =SpreadsheetApp.open(DriveApp.getFileById(URL()))///source spreadsheet ID(report sheet data)

//Get sheets from source spreadsheet

var sheetsk = SpreadsheetApp.getActiveSpreadsheet().getSheets();

//Get sheet names from spreadsheet

for (var k = 0; k < sheetsk.length ; k++ ) {
var sheetk = sheetsk[k];
var valk = sheetk.getName();
var ss = sss.getSheetByName(valk); // ss = source sheet
//Get full range of data

var range = ss.getRange(1, 1, ss.getMaxRows(), ss.getMaxColumns());

var SRange = range;
//get the data values in range
var SData = SRange.getValues();

var s123=getFileByName();

//Get destination spreadsheet id where data needs to be pasted

var tss = SpreadsheetApp.open(DriveApp.getFileById(getFileByName())); // target spreadsheet id

//get target spreadsheet  
var ts = tss.getSheetByName(valk); // ts = target sheet
     var sheetskTotal = tss.getSheets().length;
   for (var l = 0; l < sheetskTotal ; l++ ) {

      var sheetsk1 = tss.getSheets()[l];
   var old_sheetname = sheetsk1.getName();
//match source spreadsheets name with target spreadsheet,if matched then data pasted to target spreadsheet 
   if (valk === old_sheetname) {

     var lastRow = ts.getLastRow();

var ss1 = sss.getSheetByName(‘match’);

var startRow = ts.getLastRow()+1; // +1 because remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].
var startColumn = 1;
var numRows = SData.length;
var numColumns = SData[0].length;
var date = Utilities.formatDate(new Date(), “GMT+1”, “dd/MM/yyyy”)
var endDate = date

ts.getRange(startRow, startColumn, numRows, 1).setValue(date);
var FormatLastLow = ts.getLastRow();
var FormatLastCol = ts.getLastColumn();
var cell = ts.getRange(startRow, startColumn, 1, 1);
cell.setBackground(‘#FFFF00’);
ts.getRange(startRow+1, startColumn, numRows, numColumns).setValues(SData);
var cell = ts.getRange(startRow+1, startColumn, 1, numColumns+1);
cell.setBackground(‘#90EE90’);

var cell = ts.getRange(startRow+1, startColumn, FormatLastLow, numColumns+1);
//cell. setBackground(‘#008000’);
cell.setBorder(true, null, true, null, true, true);
var datas = ts.getDataRange().getValues();
for (var d=0, l=datas.length; d<l; d++) {
if (datas[d][1] === “Back to Index”) {
ts.getRange(d+1, 2).setValue(“”);
}

}
SpreadsheetApp.flush();

else
{

}

}

  //var ss1 = sss.getSheetByName(valk1); // ss = source sheet

}
function GETsheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
function URL() {
var str = SpreadsheetApp.getActiveSpreadsheet().getUrl();
var regex = new RegExp(“\/d\/(.+)\/edit”);
var res = regex.exec(str);

    return res[1];

}

function sheetcreate() {
var sss =SpreadsheetApp.open(DriveApp.getFileById(URL()))///source spreadsheet ID(report sheet data)

var sheetsk = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var k = 0; k < sheetsk.length ; k++ ) {
var sheetk = sheetsk[k];
var valk = sheetk.getName();
//SpreadsheetApp.getUi().alert(val);
var ss = sss.getSheetByName(valk); // ss = source sheet

// var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = ss.getRange(1, 1, ss.getMaxRows(), ss.getMaxColumns());

//Get full range of data
var SRange = range;
//get A1 notation identifying the range
// var A1Range = SRange.getA1Notation();
//get the data values in range
var SData = SRange.getValues();

var tss = SpreadsheetApp.open(DriveApp.getFileById(getFileByName())); // target spreadsheet
var ts = tss.getSheetByName(valk); // ts = target sheet
var sheetskTotal = tss.getSheets().length;
for (var l = 0; l < sheetskTotal ; l++ ) {

      var sheetsk1 = tss.getSheets()[l];

  // SpreadsheetApp.getUi().alert('H');
   var old_sheetname = sheetsk1.getName();
     if (valk === old_sheetname) {

}
else
{
var ts1 = tss.getSheetByName(valk);
//var ts = tss.getSheetByName(“Name of your new sheet”);

if (ts1 != null) {
   //activeSpreadsheet.deleteSheet(yourNewSheet);
}
    else
    {
    tss.insertSheet(valk);
    }
  }

}

}

  //var ss1 = sss.getSheetByName(valk1); // ss = source sheet

    return res[1];

}

function getFileByName(){

var folders1=””
var fileName = “”;
var fileInFolder = folders1
var filecount = 0;
var dupFileArray = [];
var folderID = “”;

// Get the active spreadsheet and the active sheet
var sso = SpreadsheetApp.getActiveSpreadsheet();
var ssido = sso.getId();

// Look in the same folder the sheet exists in. For example, if this template is in
// My Drive, it will return all of the files in My Drive.
var ssparents = DriveApp.getFileById(ssido).getParents();

// Loop through all the files and add the values to the spreadsheet.
var folder = ssparents.next();
var files = folder.getFiles();
var i=1;
while(files.hasNext()) {
var file = files.next();
if(URL() != file.getId()){
var idsSheet=file.getId();
return idsSheet;

 //SpreadsheetApp.getUi().alert(file.getId());
}

}
}

Leave a Reply Cancel reply