CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
J.Doe
J.Doe

Reputation: 37

Spreadsheet to copy informations from another sheets - Javascript

I'm looking way to create easier version of my code. I want to, copy information from every sheet to one sheet. First informations from first sheet, then below from second sheet etc. Now I have every each code for sheet, it is any way to make it easier? What code should use? Below my code and drawing with explanation.

function copy() {
  
 var ur = SpreadsheetApp.getActiveSpreadsheet(); // sheet where we copy data
 var urgents = ur.getSheetByName("List"); 
 var lastRow = urgents.getLastRow();
 var range = urgents.getRange("A2:B");
   range.clear(); // every time script clear data 

 var importsh = SpreadsheetApp.openById('115nqlMpW3dhI-adpWvir5RZeayM01HqvjOcDc2_yLvQ');
 
 var imbrak = importsh.getSheetByName('List_1'); // sheet from  we copy data
 var imbrak2 = importsh.getSheetByName('List_2'); // sheet from we copy data
 var lastcol = importsh.getLastColumn();
 var lastcol2 = imbrak2.getLastColumn();

 var last = imbrak.getLastRow();
 var last2 = imbrak2.getLastRow();

 var urlast = last;
 var urlast2 = last2;

    
var data = imbrak.getRange("A2:B"+last).getValues();
var data2 = imbrak2.getRange("A2:B"+last2).getValues();

  urgents.getRange("A2:B"+last).setValues(data);
  urgents.getRange(getFirstEmptyRowWholeRow3(),1,last2-1,2).setValues(data2);
               
 }

enter image description here

Regards

Upvotes: 0

Views: 174

Answers (1)

Tanaike
Tanaike

Reputation: 201358

How about this modification? Please think of this as one of several answers.

Modification points :

  • Use an array for sheet names.
    • By this, when it increases the number of sheets, you can use this script by importing the sheet names to the array.
  • All data of each sheets is imported to an array.
    • By this, the data can be imported to the destination sheet by one call of setValues().

Modified script :

function copy() {
  var ur = SpreadsheetApp.getActiveSpreadsheet(); // sheet where we copy data
  var urgents = ur.getSheetByName("List"); 
  var lastRow = urgents.getLastRow();
  var range = urgents.getRange("A2:B");
  range.clear(); // every time script clear data

  // Added
  var importsh = SpreadsheetApp.openById('115nqlMpW3dhI-adpWvir5RZeayM01HqvjOcDc2_yLvQ');
  var sheets = ['List_1', 'List_2']; // Please import sheet names here.
  var data = [];
  sheets.forEach(function(e){
    var s = importsh.getSheetByName(e);
    var values = s.getRange("A2:B"+s.getLastRow()).getValues();
    Array.prototype.push.apply(data, values);
  });
  urgents.getRange(2, 1, data.length, data[0].length).setValues(data); // Please confirm this range.
}

Note :

  • Please confirm the range of urgents.getRange(2, 1, data.length, data[0].length).setValues(data);. Because I have no information about getFirstEmptyRowWholeRow3().
    • In the current range, the data is imported to "A2:B".

If I misunderstand your question, please tell me. I would like to modify.

Upvotes: 1

Related Questions