Google Sheets Script - Looping through all sheets, and adding all values of a certain column into array to then count unique values

Question

I want to loop through all sheets (I was 57) and get all the elements from the first column,and add them into an array to then, that I can later access with ALL the ids from ALL the sheets.The problem with my code is that the ids array is not loading correctly. When I print ids.length it equals 0. So I'm guessing something is wrong on my forEach loop where it won't push the values into the array.

function countLinesPerCharacter() {
  let app = SpreadsheetApp;
  let spreadsheet = SpreadsheetApp.getActive()
  let allSheets = spreadsheet.getSheets()
  
  let targetSheet = app.getActiveSpreadsheet().getSheetByName("lines");
  
  let ids = []
  let y = 2
  //goes thrrough each sheet 
  allSheets.forEach(function(sheet){
    sheet.activate() 
    //goes through the rows 
    //row col
    
    let lastRowNumber = spreadsheet.getLastRow();
    for(let i = 0; i < lastRowNumber.length; i++) {
      let  questionID = spreadsheet.getRange(i, 1).getValue();
      ids.push(questionID) // IT WON'T LOAD THE questionID into ids ----
      y++
     }
  })
  
  targetSheet.getRange(1, 5).setValue(ids.length); //ids.length = 0
  targetSheet.getRange(1, 1).setValue("Done going through each sheet");
  
}

Add a [mcve] (the current code isn't complete an doesn't include sample input data and the corresponding expected result) — Rubén, Jul 07 '20 at 02:52
Thank you for replying. The start number of `getRange` is `1`. But it seems that in your script, `0` is the start number. And also, at `spreadsheet.getRange(i, 1).getValue()`, I think that in your script, the 1st tab is always used as `spreadsheet`. How about these? By the way, what is `let y = 3`? — Tanaike, Jul 07 '20 at 03:09
Yes I saw that and changed it to 'for(var i = 2; i < lastRowNumber.length; i++) {' but nothing changes. — maraz, Jul 07 '20 at 03:17

score 1 · Answer 1 · answered Jul 07 '20 at 07:36

Issues:

for(let i = 0; i < lastRowNumber.length; i++) {

lastRowNumber is of type Number and doesn't have a .length property.lastRowNumber.length is undefined and as i is not less than undefined, the loop never starts.

spreadsheet.getLastRow()

This only gets the first sheet's last row. Last row of the current sheet should be retrieved.

spreadsheet.getRange(i, 1)

Spreadsheet doesn't have a .getRange( number, number) method. Only sheet class does.

Modified script:

Old script:

function countLinesPerCharacter() {
  const spreadsheet = SpreadsheetApp.getActive();
  const allSheets = spreadsheet.getSheets();
  const targetSheet = spreadsheet.getSheetByName('lines');
  const ids = [];
  //goes thrrough each sheet
  allSheets.forEach(function(sheet) {
//sheet.activate();
//goes through the rows
//row col

const lastRowNumber = sheet.getLastRow();//modified getlastRow from sheet 
for (let i = 1; i <= lastRowNumber; i++) {//modified=> length removed; "<"=>"<=";i=0=>i=1
  const questionID = sheet.getRange(i, 1).getValue();//getRange from sheet 
  ids.push(questionID); 
}
  });

  targetSheet.getRange(1, 5).setValue(ids.length); //ids.length = 0
  targetSheet.getRange(1, 1).setValue('Done going through each sheet');
}

Optimized script:

function countLinesPerCharacter2() {
  const ss = SpreadsheetApp.getActive();
  const targetSheet = ss.getSheetByName('lines');
  const ids = ss
    .getSheets()
    .map(sheet => sheet.getRange(1, 1, sheet.getLastRow()).getValues())
    .flat(2);
  targetSheet.getRange(1, 5).setValue(ids.length);
}

To Read:

Frank · Answer 2 · 2020-07-07T04:00:33.160

0

Try something like this. I simplified some of your expressions. Couple notes:

Don't forget your semicolons. Good use of let throughout.
Remember that allSheets is every sheet, including targetSheet.

function myFunction() {
 let allSheets   = SpreadsheetApp.getActive().getSheets();
 let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("lines");
 
 let ids = [];
 //goes thrrough each sheet 
 allSheets.forEach(function(sheet){
   sheet.activate();
   //goes through the rows 
   //row col
   
   let rows = sheet.getRange("A:A").getValues();  // handy way to select the whole first column
   rows = rows.filter(row => row[0] !== ""); // remove blank values based on the first column (you may want to remove this)
   rows.forEach(row => ids.push(row));
 });
// console.log(ids);      // optional diagnostic
 
 targetSheet.getRange(1, 5).setValue(ids.length);
 targetSheet.getRange(1, 1).setValue("Done going through each sheet");
}

edited Jul 07 '20 at 04:00

answered Jul 07 '20 at 03:52

Frank

459
2
9

That helped but gets stuck on the first sheet. I did a for loop to print the array, and only displays the id's from the first sheet. `let x = 2 //row i want to start in for(let i = 2; i < ids.length; i++) { targetSheet.getRange(x, 2).setValue(ids[i]); //targetSheet.getRange(x, 4).setValue("debugging"); x++ }` – maraz Jul 07 '20 at 04:32
Remember that this will grab only non-blank cells in Column A of every sheet. Do you have any formula outputs or other weird cells? Uncomment the `console.log` and check its output. I made a spreadsheet with three tabs and it collected everything it was supposed to and nothing it didn't. – Frank Jul 07 '20 at 05:58

Google Sheets Script - Looping through all sheets, and adding all values of a certain column into array to then count unique values

2 Answers2

Issues:

Modified script:

To Read: