CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nathaly Wheller
Nathaly Wheller

Reputation: 25

How to Collect data from ALL sheets using google Appscript

I have script that searches through an entire workbook for a specific name and returns all the data on that name. The script works, but only collects data from 1 sheet within the workbook.

I searched for some code to assist me getting all the sheet names. So I have code that does that, but for some reason it still only returns from 1 sheet.

The code below collects all the sheet names. This function is then called in the query function. I Suspect that this is where the issue is occuring

function sheetnames() {

var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (var i=0 ; i < sheets.length ; i++) {
    var name = sheets[i].getName();

    var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
    var values = data.getRange(4, 1, data.getLastRow(),

    data.getLastColumn()).getValues();
    out.push(values);
    }

return out; 
}

This function then searches for the requested data.

function query() {

var Sheet = SpreadsheetApp.getActiveSpreadsheet();

var searchSheet = Sheet.getSheetByName("Search");   
var searchByName = searchSheet.getRange(4, 8).getValue();  

var uses = sheetnames();
var output = new Array();

var i = 0;
var r = 0;


do{
  var from = uses[i];

  do{
    var row = from[r];

    if(row == null){
      r++;
      continue;
    }

    if(searchByName != null ){
      var newName = row[7];
      if(newName == searchByName){
        output.push(row);
      } 
    }
     r ++;
    }while(r < from.length);
    i ++;

  }while(i < uses.length);

return output;
}

This part just prints the data into the cells and is attached to a search drawing, which runs the function in the sheet.

function search() {

 var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Search");

 var data = query();

 var count1 = 0; 


do{
  var subData = data[count1];
  var count2 = 0;

  do{
    var setTo = subData[count2];
    Sheet.getRange((count1 + 5), (count2 + 1)).setValue(setTo);
    count2 ++;
    }while(count2 < subData.length);

   count1 ++;
   }while(count1 < data.length); 
}

The sheet is called the "Daily Payments Sheet." As you can imagine there is A LOT of data. Each sheet name is named by the month and the year that the payment occurred. The more consistent customers would obviously make purchases in more than one month.

So when searching for a customers name, I only get 1 month (1 sheet's data) returned. We have data from May 2018 till date, so again, the script doesn't collect from all the sheets.

Upvotes: 0

Views: 189

Answers (1)

ra89fi
ra89fi

Reputation: 1245

Your code is not very readable so I figured some things on my own and simplified it. Things I assume - your search term is in 'Search' sheet column H4 and you want to search all sheets for this term in H4 column and write those out in 'Search' sheet after 4th row. Try this.

// return all rows from all sheets except Search sheet
function sheetValues(ss) {
  var out = [];
  var sheets = ss.getSheets();

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (sheet.getName() == 'Search') continue;
    var values = sheet.getRange(4, 1, sheet.getLastRow() - 3, sheet.getLastColumn()).getValues();
    out.concat(values);
  }

  return out;
}
// search all rows for given term and return results
// look for term in H column of every row
function query(ss, term) {
  if (!term) return;

  var values = sheetValues(ss);
  var output = [];

  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var name = row[7]; // 7 = col H
    if (name == term) {
      output.push(row);
    }
  }

  return output;
}
// get search results and print into Search sheet
function search() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Search');
  var searchByName = sheet.getRange(4, 8).getValue(); // search term is in H4 cell

  var data = query(ss, searchByName);

  sheet.getRange(5, 1, sheet.getLastRow() - 4, sheet.getLastColumn()).clearContent();
  sheet.getRange(5, 1, data.length, data[0].length).setValues(data);
}

Upvotes: 0

Related Questions