Import Data From multiple sheet with a list of ID and specifics column-CodePudding

I have about many spreadsheet work with my partners and each partner use 1 spreadsheat for manage data. I using a table to store all ID, Sheet name and column store data I need to import to my master sheet. This is my Spreadsheet:

I using Appscript to do import data by read values in this table with 2 loop, I see it work to slow How could I speed up it? this is my script


function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source Link');
var master = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master') ;
const dstLr = getLastPopulatedRow(ss); //get last row of Source Link
const target_sheet_name = ss.getRange(2,6,dstLr-1,1).getValues(); //Get data in Column F input to array
const ids = ss.getRange(2,1,dstLr-1,1).getValues(); //get data in column A input to array
const ebayID = ss.getRange(2,2,dstLr - 1,1).getValues(); //get cloumn index at Column B input to array
const team = ss.getRange(2,3,dstLr-1,1).getValues(); //get column containt team name at column C input to array
const linkOrder = ss.getRange(2,4,dstLr-1,1).getValues(); //get cloumn containt Link Order at column D input to array
const tracking = ss.getRange (2,5,dstLr-1,1).getValues(); //get column containt Tracking at column E input to array
const benCO = ss.getRange(2,7,dstLr,1).getValues();

    ids.forEach((id, i) => {
      const srcSheet = SpreadsheetApp.openById(id).getSheetByName(target_sheet_name[i]);
      const lr = getLastPopulatedRow(srcSheet);
      const srcCol = [ebayID[i], team[i], linkOrder[i],tracking[i]].flat();
          for(var j = 0; j < srcCol.length; j  ) {
          const destCol = [1,2,3,4];
          const destCol2 = [5];
          destCols = destCol[j]
          const srcRange = srcSheet.getRange(3, srcCol[j], lr); // Origin range to copy
          const values = srcRange.getValues(); // Getting values from origin column
          const destRange = master.getRange(2, destCols, lr);
          master.clearContents;
          destRange.setValues(values);
        }
      
    })
}

function getLastPopulatedRow(sheet) {
  var data = sheet.getDataRange().getValues();
  for (var i = data.length-1; i > 0; i--) {
    for (var j = 0; j < data[0].length; j  ) {
      if (data[i][j]) return i 1;
    }
  }
  return 0;
}

I also need add value of G column for each data I import from ID and Sheet name at colum A and column F, but when I try set value for column E at Master sheet I got error that "data have 1 but destination range have 582" Could Some one give me advise

That's what I try

ids.forEach((id, i) => {
  const srcSheet = SpreadsheetApp.openById(id).getSheetByName(target_sheet_name[i]);
  const lr = getLastPopulatedRow(srcSheet);
  const srcCol = [ebayID[i], team[i], linkOrder[i],tracking[i]].flat();
      for(var j = 0; j < srcCol.length; j  ) {
      const destCol = [1,2,3,4];
      const destCol2 = [5];
      destCols = destCol[j]
      const srcRange = srcSheet.getRange(3, srcCol[j], lr); // Origin range to copy
      const values = srcRange.getValues(); // Getting values from origin column
      const destRange = master.getRange(2, destCols, lr);
      master.clearContents;
      destRange.setValues(values);
    }
  const coName = [];
  coName.push(benCO[i]);
  master.getRange(2,5,lr,1).setValues(coName);
})

and this is error:

CodePudding user response：

From your question and samples, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceLink = ss.getSheetByName('Source Link');
  const master = ss.getSheetByName('Master');
  const srcValues = sourceLink.getRange("A2:G"   sourceLink.getLastRow()).getValues();
  const header = ["Order ID", "Team", "ID", "ID TRACKING#"];
  const values = [header, ...srcValues.flatMap(r => {
    const sheet = SpreadsheetApp.openById(r[0]).getSheetByName(r[5]);
    const [, h, ...v] = sheet.getDataRange().getValues();
    const temp = h.map(e => e.trim());
    const indexes = header.map(e => temp.indexOf(e));
    return v.map(r => indexes.map(i => r[i]));
  })];
  master.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

In this sample script, first, the header is declared and the values are retrieved using the header from each sheet. And, the populated values are put into the destination sheet.
In this sample script, the header is used from your sample Spreadsheet. So, when you change the header titles, the result values might not be able to be used. Please be careful about this.

Reference:

map()

Added:

From your following reply,

I got messenger error at this line: const temp = h.map(e => e.trim()); messenger said: TypeError: e.trim is not a function

My proposed script is for your provided Spreadsheet, and when I tested my proposed script no error occurs. From your reply, I guessed that your Spreadsheet might be different from your provided Spreadsheet. If my understanding is correct, from your error message, I'm worried that the header rows might be changed. In your provided Spreadsheet, 1st 2 rows are header rows. But, if the header row is only the 1st row, the error might occur because the row values include the date object in the 2nd row. If my understanding is correct, can you test the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceLink = ss.getSheetByName('Source Link');
  const master = ss.getSheetByName('Master');
  const srcValues = sourceLink.getRange("A2:G"   sourceLink.getLastRow()).getValues();
  const header = ["Order ID", "Team", "ID", "ID TRACKING#"];
  const values = [header, ...srcValues.flatMap(r => {
    const sheet = SpreadsheetApp.openById(r[0]).getSheetByName(r[5]);
    // const [, h, ...v] = sheet.getDataRange().getValues();
    let h;
    let [h1, h2, ...v] = sheet.getDataRange().getValues();
    if (h2.some(e => e instanceof Date)) {
      h = h1;
      v = [h2, ...v];
    } else {
      h = h2;
    }
    const temp = h.map(e => e.trim());
    const indexes = header.map(e => temp.indexOf(e));
    return v.map(r => indexes.map(i => r[i]));
  })];
  master.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

If the same error occurs and another error occurs, can you provide the sample Spreadsheet for correctly replicating the issue? By this, I would like to confirm it.