google-spreadsheet - importrange - query contains google sheets

= Sintaxis QUERY al trabajar con datos enviados a través de Google Form

Question

La consulta siguiente ''funciona'' línea por línea (cuando se rellena manualmente), pero no se ''autocompleta'' cuando se agregan nuevos datos a la ''hoja de respuestas''. Los datos que se agregan a la "hoja de respuestas" provienen de un formulario de Google.

=QUERY(Sheet2!$A$1:K; CONCATENATE("SELECT B, C WHERE A = ", responses!B1), 0)

Pregunta
Uno - ¿Cómo ajusto esta consulta para que se ''autocomplete'' cuando se agreguen nuevos datos a la ''hoja de respuestas'' mediante un formulario de Google?

Si esto no es posible, ¿alguna sugerencia?

Aquí hay un enlace a la hoja de cálculo de Google con la que estoy trabajando

Gracias por su tiempo y asistencia,
Todd
Profesor de instituto

Answer 1

Una forma de evitarlo es usar Google App Script (GAS) .

Lo que es posible es configurar una función onEdit que agregará la fórmula a la celda requerida cuando se agreguen nuevos datos.

Usando un código muy rápido (abajo). Esto aplicará su fórmula cuando la hoja de cálculo se actualice.

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("responses");
var lastRow = ws.getLastRow();
var studentID = ws.getRange(lastRow, 4).setFormula(''=QUERY(Sheet2!$A$1:K; CONCATENATE("SELECT B, C WHERE A = ", responses!B''+lastRow+''), 0)'');
}

Copié tu hoja de cálculo y la configuré aquí -> ENLACE Puedes probarla simplemente copiando una nueva identificación de estudiante en la columna B y verás que la información del estudiante se extrae de la segunda hoja.

Answer 2

Otra opción es usar VLOOKUP aplicado como una fórmula de matriz . Esto se rellenará automáticamente en la columna a medida que se envíen los formularios. Entonces en la fila 2:

=ArrayFormula(IFERROR(VLOOKUP(B2:B;Sheet2!A2:C;SIGN(ROW(B2:B))*{2,3};0)))

o puede ingresar esto en la fila 1, que también rellenará los encabezados, y es un poco más estanco cuando las filas se insertan / eliminan en ciertas situaciones:

=ArrayFormula(IF(ROW(A:A)=1;{"First Name","Last Name"};IFERROR(VLOOKUP(B:B;Sheet2!A2:C;SIGN(ROW(B:B))*{2,3};0))))