How can I gather all data from my sheets?-CodePudding

I have a Google Spreadsheet with a lot of sheets containing data, it goes between 1-2 to 40-50 rows per sheet.

I must also note that I'm not a programmer and I am working on this based on Google results.

My goal is to have a "Master" sheet that collects the data from all other sheets and a "Filter" sheet, where I can filter all that "Master" data based on certain values.

I have been able to get it "sort of" working but due to the amount of data, the script times-out most of the time and the main problem is, column headers are not always in the same order and I get data that is hard to filter and work on.

Here is what has been working so far:

const URL_LIST_SHEET_NAME = "URL_LIST!"; 
const FILTER_SHEET = "Filter";
const MASTER_NAME = "Master";
const TEMPLATE_NAME = "Template";

function GET_DATA() {
  const dataSheets = SpreadsheetApp.getActiveSpreadsheet()
      .getSheets()

for (let s in dataSheets) {
    let sheetNm = dataSheets[s].getName();
    // Should skip sheets that I don't need, to reduce the time but not really working ???
    if (sheetNm === FILTER_SHEET || sheetNm === URL_LIST_SHEET_NAME || sheetNm === MASTER_NAME || sheetNm === TEMPLATE_NAME) { continue; }
  const ranges = dataSheets
      .map(info => info.getRange("A2:F30")); //if I put info.getDataRange() here it doesn't work
return ranges
      .reduce((result, range) => result.concat(range.getValues()), []); 
  }
}

What I've been trying to do is to get data sorted based on header rows by combining different solutions I found. So far it's not working and even when I get it to successfully execute there is nothing in the "Master" sheet. The most common error I get is "TypeError: sheets.getDataRange is not a function"

const URL_LIST_SHEET = "URL_LIST!";
const FILTER_SHEET = "Filter";
const MASTER_NAME_SHEET = "Master";
const TEMPLATE_NAME_SHEET = "Template";
const target_headers = ['firstName', 'companyName', 'BadLinkURL', 'DiscreditProofURL', 'email', 'Niche']; 
// Headers I need to sort by, they are not always in the same columns and I need them to be for exporting

function GetColumnValues() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for (var i=0; i<sheets.length; i  ) {

  for (let s in sheets) {
    // Gets sheet name.
    let sheetNm = sheets[s].getName();
    // Skips sheets.
    if (sheetNm === FILTER_SHEET || sheetNm === URL_LIST_SHEET || sheetNm === MASTER_NAME_SHEET || sheetNm === TEMPLATE_NAME_SHEET)
     { continue; }
  
    const range = sheets[i].getDataRange(); //With varying data ranges, triming the completely blank rows is a must
    const values = range.getValues();
    const headers = values.shift();
    const columnIndex = headers.indexOf(target_headers);
    const columnValues = values.map(row => row[columnIndex]);
    return columnValues;
  }
  }
}

Considering I'm getting timeouts, can I process this in batches of ~30 sheets until all have been processed? The number of sheets in this workbook is 100 and will only increase so I think that this will be a serious issue for execution time.

CodePudding user response：

First problem I see in your GET_DATA function is that you have a nested a loop inside a loop... and it's the same loop (going over all the sheets). So you should do that only once.

Better try something like

const excluded = [
  FILTER_SHEET,
  URL_LIST_SHEET_NAME,
  MASTER_NAME,
  TEMPLATE_NAME
]
const RANGE = "A2:F30"

function GET_DATA() {
  const dataSheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  const values = dataSheets
      .filter(dataSheet => excluded.indexOf(dataSheet.getName()) === -1)
      .map(info => info.getRange(RANGE))
      .reduce((result, range) => result.concat(range.getValues()), []); 

  return values
}

CodePudding user response：

The @ValLeNain's answer is correct, I would just add the same suggestion on the second function GetColumnValues(). You have 2x for-loop and mixing it, that's causing your error TypeError: sheets.getDataRange is not a function and probably not helping your executing time.

Modified code:

const URL_LIST_SHEET = "URL_LIST!";
const FILTER_SHEET = "Filter";
const MASTER_NAME_SHEET = "Master";
const TEMPLATE_NAME_SHEET = "Template";
const target_headers = ['firstName', 'companyName', 'BadLinkURL', 'DiscreditProofURL', 'email', 'Niche']; 
// Headers I need to sort by, they are not always in the same columns and I need them to be for exporting

function GetColumnValues() {

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  //one for-loop removed

  for (var i = 0; i < sheets.length; i  ) {

    let sheetNm = sheets[i].getName(); //modified
    if (sheetNm === FILTER_SHEET || sheetNm === URL_LIST_SHEET || sheetNm === MASTER_NAME_SHEET || sheetNm === TEMPLATE_NAME_SHEET) { 
      continue; 
    }
  
    var range = sheets[i].getDataRange(); 
    var values = range.getValues();
    var headers = values.shift();
    var columnIndex = headers.indexOf(target_headers); //not tested
    var columnValues = values.map(row => row[columnIndex]); //not tested
    return columnValues;
  }
}

Note:

Spreadsheet has 200 sheets limitation Spreadsheet limitations
You will face problems with executing time anyway with a file this large