google sheets - Script to VLOOKUP Summary Column From Multiple Tabs Pull Data wrt Value From Each Tab

Question

I've a sheet where there are multiple tabs. I want to summarize first column and 11th column of all tabs. I would like to merge the first column so there are only unique values. Then pull the data from first tab as 2nd column and second tab as 3rd column and so on. The sheet is here

What I've tried so far: I have tried to use formula which is both manual and ever increasing. The tab name is "Desired". For the first column I used query formula. I used only for two tabs since I've more tabs.

=UNIQUE(query({indirect(address(2,1,1,1,TEXT(B1,"ddmmmyyy"))&":A"),indirect(address(2,1,1,1,TEXT(C1,"ddmmmyyy"))&":A")},"Select Col1 where Col1 is not null"))

Then for each column I used vlookup

=VLOOKUP($A2,{indirect(address(2,1,1,1,TEXT(B$1,"ddmmmyyy"))&":A"),indirect(address(2,9,1,1,TEXT(B$1,"ddmmmyyy"))&":I")},2,0)

Update: I managed to solve the first part with the following code

    function SUMMARIZE2() {
  const ss = SpreadsheetApp.getActive();
  const weekSheets = ss.getSheets().filter(sheet => sheet.getName().endsWith("2020"));
  const summarySheet = ss.getSheetByName("Summary");
  let weekData = weekSheets.map(weekSheet => {
    return weekSheet.getRange(2, 1, weekSheet.getLastRow() - 1).getValues();    
  }).flat();
const getUnique_ = array2d => [...new Set(array2d.flat())];
  uniqueValues = getUnique_(weekData).map(e => [e]);
 
 var header = [["Company Name"]];
      summarySheet.getRange("A1:A1").setValues(header);
      summarySheet.getRange("A2:A"+(uniqueValues.length+1)).setValues(uniqueValues); 
}

I appreciate the replies.

Answer 1

You could do the following:

1. Get the headers of the destination sheet, starting with Company Name and followed by the different sheet names.
2. Get the unique names of the companies (2D array with inner arrays having a single element - the company name).
3. Iterate through all sheets ending with 2020. For each sheet, follow steps 4-5.
4. Iterate through all rows in the sheet. For each row, look for the index of the company (column A) in the array of companies from step 2, using findIndex.
5. On the index found in 4, push the value from column 11 to the corresponding inner array (from step 2).
6. Concatenate the headers from 1 with the values from 4-5.
7. Since the different inner arrays have different lengths, find the array with maximum length and add elements to the others so that all inner arrays have the same length (that's necessary when using setValues).
8. Use setValues to write all your summarized values to your destination sheet.

Code snippet:

function summarizeAllData() {
  const ss = SpreadsheetApp.getActive();
  const weekSheets = ss.getSheets().filter(sheet => sheet.getName().endsWith("2020"));
  const summarySheet = ss.getSheetByName("Summary");
  const headers = ["Company Name"].concat(weekSheets.map(weekSheet => weekSheet.getName()));
  let values = weekSheets.map(weekSheet => {
    return weekSheet.getRange(2, 1, weekSheet.getLastRow() - 1).getValues();    
  }).flat();
  values = [...new Set(values)];
  weekSheets.forEach(weekSheet => {
    const sheetValues = weekSheet.getRange(2, 1, weekSheet.getLastRow() - 1, 11).getValues();
    sheetValues.forEach(row => {
      const companyIndex = values.findIndex(company => company[0] === row[0]);
      if (values[companyIndex]) values[companyIndex].push(row[10]);
    });
  });
  values = [headers].concat(values);
  const lengths = values.map(a=>a.length);
  const maxLength = lengths[lengths.indexOf(Math.max(...lengths))];
  values.forEach(row => {
    for (let i = row.length; i < maxLength; i++) {
      row.push("");
    }               
  });
  summarySheet.getRange(1,1,values.length,values[0].length).setValues(values);
}

Notes:

• When I tried to copy all the data, I got an error saying that the resulting spreadsheet would reach the 5 million cell limit. Because of this, I created a new spreadsheet instead of writing to the Summary sheet: SpreadsheetApp.create("YOUR NEW SPREADSHEET").getSheets()[0].getRange(1,1,values.length,values[0].length).setValues(values);. Replace last line in the code with that if you get this error.
• The sheet names are not sorted chronologically. Since the sheet names are not dates themselves, sorting them is not immediate, so I skipped that part (it's not an insurmountable difficulty, so if you have problems with this, I'd suggest posting another question).
• There are sheets ("tabs") which have repeated company names, each with a different value in column 11. In cases like this, the script will retrieve the value corresponding to the first row that is found. Please be careful about this.