Script to copy workbook to workbook data only

Question

Script would run in the source workbook.

function CopyDataToNewFile() {

 var sss = SpreadsheetApp.openById('1yeS6_qhURUYHVkImLNMjdGUeMWhPDwQKdLNgpygiwG4'); // sss = source spreadsheet

 var ss = sss.getSheetByName('Sheet1'); // ss = source sheet

 //Get full range of data

 var SRange = ss.getDataRange();

 //get A1 notation identifying the range

 var A1Range = SRange.getA1Notation();

 //get the data values in range

 var SData = SRange.getValues();



var tss = SpreadsheetApp.openById('1KQBsMJ0vgBQwkM89d166RPyVahIZY3DRSOWVCB4zapE'); // tss = target spreadsheet

 var ss = SpreadsheetApp.getActiveSpreadsheet();

var first = ss.getSheetByName('Sheet1');

sheet.clear({ formatOnly: false, contentsOnly: true });



 var tss = SpreadsheetApp.openById('1KQBsMJ0vgBQwkM89d166RPyVahIZY3DRSOWVCB4zapE');

 var ts = tss.getSheetByName('Sheet1'); // ts = target sheet

 //set the target range to the values of the source data

 ts.getRange(A1Range).setValues(SData);

}

Would set to trigger on form submit. Would copy the data from a sheet in one workbook to a sheet in another workbook. Format of both sheets will be same AND must remain unaltered. Row & column count/size will be same on each sheet.

You need to edit this question so that your code is properly blocked. In the editor, select the code and then hit the button at top with {}. — Joshua Dannemann
Importrange() function of spreadsheet does not fit your need ? — St3ph
it takes too long - want to see if a script reduces time (6 mins) — HarryHoe

St3ph St3ph · Accepted Answer · 2015-09-15T08:33:13

Here a code that will go through all the sheet of your source workbook and copy data in the current workbook. Sheet name will be the same of source spreadsheet and only data are copied.

EDIT : Code changed to not copy but import data from spreadsheet to a dest spreadsheet where we assume that the sheet is already created. If sheet does not exist we create it.

function importWorkBook(){
  var idSource = "IdOfTheSheetSource";
  var sheets = SpreadsheetApp.openById(idSource).getSheets();
  var current = SpreadsheetApp.getActiveSpreadsheet();
  for(var i in sheets){
    var sheet = sheets[i];
    var data = sheet.getDataRange().getValues();
    var destSheet = current.getSheetByName(sheet.getName());
    if(destSheet){
      destSheet.getRange(1,1,destSheet.getMaxRows(),destSheet.getMaxColumns()).clearContent();
      destSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log(sheet.getName() + " imported");
    }else{
      var newSheet = current.insertSheet(sheet.getName());
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log(sheet.getName() + " created");
    }
  }
}

Stéphane

Script to copy workbook to workbook data only

1 Answers