With this script I can import 1 spreadsheet into another spreadsheet in a tab.

function importA() {
var values1 = SpreadsheetApp.openById('xxxurl').
  getSheetByName('xxxname').getRange('A1:DE300').getValues(); 
SpreadsheetApp.getActive().getSheetByName('TAB').
  getRange(1,1,values1.length,values1[0].length).setValues(values1)
  }

Now, I would to import 3 spreadsheets one below the other (in the same tab), preserving any increase in the rows of each sheet at the same time.

I could I proceed?

CodePudding user response：

I've used getSheetByName instead of openByID but you can get the idea.

function test() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ["Sheet1","Sheet2","Sheet3"];
    for( var i=0; i<sheets.length; i   ) {
      importA(sheets[i]);
    }
  }
  catch(err) {
    console.log(err);
  }
}

function importA(name) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh1 = ss.getSheetByName("TAB");
    sh1.clear() // if you want to erase old data in TAB
    var sh2 = ss.getSheetByName(name);
    var r1 = sh1.getDataRange();
    var r2 = sh2.getDataRange();
    // to prevent a blank line at the top
    var i = r1.getNumRows() === 1 ? 0 : 1;
    sh1.getRange(r1.getNumRows() i,1,r2.getNumRows(),r2.getNumColumns()).setValues(r2.getValues());
  }
  catch(err) {
    console.log(err);
  }
}

CodePudding user response：

Merge all spreadsheets sheets into one sheet

function mergeSpreadsheetsAllIntoOneSheet() {
  const ss = SpreadsheetApp.getActive();
  ['id1', 'id2', 'id3'].forEach((id, i) => {
    let sss = SpreadsheetApp.openById(id);
    sss.getSheets().forEach(sh => {
      let vs = sh.getDataRange().getValues();
      let a = [...Array.from(new Array(vs[0].length).keys(), x => (x == 0)?sss.getName():sh.getName())];
      vs.unshift(a);
      let ash = ss.getSheets()[0];
      if (vs && vs.length > 0) {
        ash.getRange(ash.getLastRow()   1, 1, vs.length, vs[0].length).setValues(vs);
      }
    });
  });
}