CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptread-data
Josué Pérez
Josué Pérez

Reputation: 21

Read multiple sheets using Google AppScript

I've been trying to make a search box, by typing the ID and then it will display different information being pulled by the script, far I only can read one sheet from the script. Can it be possible to read more than one sheet? Gracias

On line 9, I want to read more than one Sheet, for example ["Data","Data2","Data3"];

// Buscar
var NUM_COLUMNA_BUSQUEDA = 0;
function Buscar() {

  var hojaActiva = SpreadsheetApp.getActiveSpreadsheet();
  var formulario = hojaActiva.getSheetByName("Formulario"); // Nombre de hoja del formulario
 
  var valor = formulario.getRange("B3").getValue();
  var valores = hojaActiva.getSheetByName("Datos").getDataRange().getValues(); // Nombre de hoja donde se almacenan datos
  for (var i = 0; i < valores.length; i++) {
     var fila = valores[i];
    if (fila[NUM_COLUMNA_BUSQUEDA] == valor) {
      
      formulario.getRange("B6").setValue(fila[0]);
      formulario.getRange("B8").setValue(fila[5]);
      formulario.getRange("B10").setValue(fila[11]);
      formulario.getRange("D6").setValue(fila[14]);
      formulario.getRange("D8").setValue(fila[16]);
      formulario.getRange("D10").setValue(fila[17]);
    }
  }
}

Upvotes: 0

Views: 54

Answers (1)

Cooper
Cooper

Reputation: 64062

function SearchAllShownSheets() {
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets().filter(sh => !sh.isSheetHidden());
  var r = SpreadsheetApp.getUi().prompt("Search Dialog", "Enter Search String Here", SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    shts.forEach(sh => {
      sh.createTextFinder(r.getResponseText()).findAll().forEach(r => {
        //loops through all of found ranges in each non hidden sheet
      })
    })
  }
}

Upvotes: 0

Related Questions