How collect data from unprotected sheets in the workbook in google sheets?-CodePudding

How can I collect data from all the sheets of the table?

Through apps script, I get all the unprotected sheets on the book, and the width of the range with data on sheets with the same structure.

function GetRangeArray() { 
  var sheets = SpreadsheetApp.getActive().getSheets();
  var counter = 0;
  var StrRange = "";
  var Result = new Array();

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

    if (sheets[i].getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]){
      continue;
    };
    
    counter  ;
    if ( counter == 1 ) {
      LastColumn = NumRetLetra(sheets[i].getLastColumn());
    }

        StrRange = "'"   sheets[i].getName()   "'!A2:"   LastColumn;
        Result.push([StrRange]); 
  }
    
  return Result;

function NumRetLetra(column){
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp   65)   letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

On the protected "Total" sheet, I enter the formula into cell A1 =GetRangeArray().

=query({INDIRECT(A1);INDIRECT(A2)};"SELECT Col1, Col2")

But I get an error: The result is not fully displayed. Add rows (6) to the table.

I try, where A1 - key, A3 - formula =GetRangeArray()

=QUERY({IMPORTRANGE(A1;A3);IMPORTRANGE(A1;A4)};"SELECT Col1, Col2")

But I get an error: The result is not fully displayed. Add rows (6) to the table.

If i use:

=QUERY({IMPORTRANGE(A1;A3)};"SELECT Col1, Col2")

=query({INDIRECT(A3)};"SELECT *")

This works, but only for one of the sheets.

CodePudding user response：

If the only thing you need to do, is to collect all data form all sheets, that is easy:

function getAllData() {
  const sss = SpreadsheetApp.getActiveSpreadsheet(); // get active spreadsheet object.
  const sheets = sss.getSheets(); // get all sheet objects of the active spreadsheet as an array.
  const results = {}; // creat an object to hold the results.
  for (const sheet of sheets) { // use for...of loop to iterate the array of all sheets.
    const sheetName = sheet.getName(); // get the name of each sheet.
    results[sheetName] = sheet.getDataRange().getValues(); // get all the data of each sheet, and put it into the results object with the sheet name as key.
  }
  console.log(JSON.stringify(restuls)); // show all data in you console.
}

Well, since you need to do the checking of protection, this would do:

const sss = SpreadsheetApp.getActiveSpreadsheet(); // get active spreadsheet object.

function getAllData() {
  const sheets = sss.getSheets(); // get all sheet objects of the active spreadsheet as an array.
  const results = {}; // creat an object to hold the results.
  for (const sheet of sheets) { // use for...of loop to iterate the array of all sheets.
    const protection = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)[0]; // get protection statue.
    if (protection && !protection.canEdit()) continue; // if the page is protected, skip it.
    const sheetName = sheet.getName(); // get the name of each sheet.
    results[sheetName] = sheet.getDataRange().getValues(); // get all the data of each sheet, and put it into the results object with the sheet name as key.
  }

  const maxLen = 0; // set a value to keep track of the longest row length.
  for(const value of Object.values(results)) { // iterate all data of each sheet.
    for(const row of value) { // iterate each row of the sheet values.
      if(row.length > maxLen) maxLen = row.length; // check if the length of row is bigger than the maxLen value, if true, replace that value.
      if(row.every(col => col === '')) continue; // if the row is empty, remove it.
    }
  }

  results.arr = []; // set a place holder for the output 2D array.
  for(const value of Object.values(results)) { // iterate all data of each sheet again.
    for(const row of value) { // iterate each row of the sheet values again.
      if(maxLen > row.length) results.arr.push(row.concat(new Array(maxLen - row.length))); // if the length of a row is smaller than the maxLen value, insert empty values into the array.
      else results.arr.push(row); // push the row value to the output array place holder.
    }
  }

  return results.arr; // output to spreadsheet.
}

If you are calling function from the spreadsheet as a custome function, just modify your data structure into a 2D array, it can than be directly output to spreadsheet without doing all those indirect functions.

CodePudding user response：

Modified my script according to the answer above. It turned out the following:

function GetRangeArray() { 
    const sheets = SpreadsheetApp.getActive().getSheets();
    var sheetData = new Array();
    const Result = new Array();
    
    for (sheet of sheets) {
        if (sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]) continue;
        sheetData = sheet.getDataRange().getValues();
        Object.values(sheetData).forEach(function (rowData, index) {
          if (index == 0) return; // without headers
          rowData.splice(1, 0, sheet.getName()); // sheet names as column for data indentify in total sheet.
          Result.push(rowData);
        });
      }
   return Result;
}