Google Appscript- Combine data from 2 different tabs from a Gsheet into master Gsheet-CodePudding

I want to combine data from 2 different tabs (copy and paste from 'track1' tab, then below it copy and paste from 'track2) but my script just return from 1 tab.

Process is:

Copy from 'track1' of 'Gsheetxx'
Paste into this master sheet
Then copy from 'track2' of 'Gsheetxx'
Paste it below 'track1' data

Below is my script:

    /** COPY AND PASTE FROM DASHBOARD TO MASTER SHEET **/
function copypaste(){

  // get the name of today's tab
  var fileName = '[Track1]'
  var fileName2 = '[Track2]'
  Logger.log(fileName); 

  // copy of track data-copy from 'track1' and 'track2' tab
  var fileID = 'Gsheetxx'
  var sheetDash = SpreadsheetApp.openById(fileID);
  var sheetDash2 = SpreadsheetApp.openById(fileID);
  var tabDash = sheetDash.getSheetByName(fileName);
  var tabDash2 = sheetDash2.getSheetByName(fileName2);
  var lastRowDash = my_getLastRow(tabDash,"A:AI");
  var lastRowDash = my_getLastRow(tabDash2,"A:AI");
  var rangeDash = tabDash.getRange("A2:AI"   lastRowDash);
  var rangeDash2 = tabDash2.getRange("A2:AI"   lastRowDash);  
  var valuesDash = rangeDash.getValues();
  var valuesDash2 = rangeDash2.getValues();

  // paste in current master tab
  var tabIngest = SpreadsheetApp.getActive().getSheetByName("ingestion");
  var clearRangeIngest = tabIngest.getRange("A2:AJ"); 
  var rangeIngest = tabIngest.getRange("B2:AJ"   lastRowDash);
  clearRangeIngest.clearContent();
  rangeIngest.setValues(valuesDash);


// paste in current master tab
  var tabIngest = SpreadsheetApp.getActive().getSheetByName("ingestion");
  var clearRangeIngest = tabIngest.getRange("A2:AJ"); 
  var rangeIngest = tabIngest.getRange("B2:AJ"   lastRowDash);
  clearRangeIngest.clearContent();
  rangeIngest.setValues(valuesDash2);

}

/** GET LAST ROW **/
function my_getLastRow(sheet,rangeString) {
  var lr_rng = sheet.getRange(rangeString).getValues();
  var lrIndex;
    
  for(var i = lr_rng.length-1; i>=0; i--){
      lrIndex = i;
    if(!lr_rng[i].every(function(c){return c == "";})){
    break;
    }
  }
  return lrIndex   1;
}

CodePudding user response：

In your script, how about the following modification?

From:

// paste in current master tab
var tabIngest = SpreadsheetApp.getActive().getSheetByName("ingestion");
var clearRangeIngest = tabIngest.getRange("A2:AJ"); 
var rangeIngest = tabIngest.getRange("B2:AJ"   lastRowDash);
clearRangeIngest.clearContent();
rangeIngest.setValues(valuesDash);


// paste in current master tab
var tabIngest = SpreadsheetApp.getActive().getSheetByName("ingestion");
var clearRangeIngest = tabIngest.getRange("A2:AJ"); 
var rangeIngest = tabIngest.getRange("B2:AJ"   lastRowDash);
clearRangeIngest.clearContent();
rangeIngest.setValues(valuesDash2);

To:

var values = [...valuesDash, ...valuesDash2];
var tabIngest = SpreadsheetApp.getActive().getSheetByName("ingestion");
tabIngest.getRange("A2:AJ").clearContent();
tabIngest.getRange(2, 2, values.length, values[0].length).setValues(values);

In this modification, the values of valuesDash and valuesDash2 are merged and put to the sheet.

As another approach, when Sheets API is used, the values can be retrieved from the several sheets by one API call. Using this, your script is as follows. If you use this script, please enable Sheets API at Advanced Google services.

function copypaste() {
  var fileName = '[Track1]';
  var fileName2 = '[Track2]';
  var fileID = 'Gsheetxx';

  // 1. Retrieve values from 2 sheets in 'Gsheetxx'.
  var values = Sheets.Spreadsheets.Values.batchGet(fileID, {ranges: [fileName, fileName2].map(f => `'${f}'!A2:AI`)}).valueRanges.flatMap(e => e.values.filter(r => r.join("") != ""));

  // 2. Put the values to "ingestion" sheet in the active Spreadsheet.
  var ss = SpreadsheetApp.getActive();
  var tabIngest = ss.getSheetByName("ingestion");
  tabIngest.getRange("A2:AJ").clearContent();
  SpreadsheetApp.flush();
  Sheets.Spreadsheets.Values.update({values}, ss.getId(), "'ingestion'!B2", {valueInputOption: "USER_ENTERED"});
}

References:

CodePudding user response：

insert into master tab

function copypaste() {
  const ss = SpreadsheetApp.openById('Gsheetxx');
  const sh1 = ss.getSheetByName('[Track1]');
  const sh2 = ss.getSheetByName('[Track2]');
  const vs1 = sh1.getRange(2, 1, sh1.getLastRow() - 1, 35).getValues();
  const vs2 = sh2.getRange(2, 1, sh2.getLastRow() - 1, 35).getValues();
  let a = [];
  a.push(vs1);
  a.push(vs2);
  a.flat(1);
  const msh = ss.getSheetByName("ingestion");
  msh.getRange(2,2,a.length,a[0].length).setValues(q)
}