4

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the responses sheet. The data being added to the responses sheet comes from a Google form.

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

How do I adjust this Query so it will 'autofill' down when new data is added to the responses sheet by a Google form?

If this is not possible, any suggestions?

Here is a link to the Google Spreadsheet I'm working with.

Improve this question

2 Answers 2

Reset to default
3

You would need a Google Apps Script to automatically add your QUERY to new rows.

The following script runs when a form is submitted, and populates the D column with the formula from the previous row. That is, before running the script, you need to enter your formula in the first row.

function onFormSubmit(event) {
  Logger.log("Event: " + event.changeType);
  Logger.log("New row added");
  var sheet = event.range.getSheet();
  var addedRowIdx = event.range.getRow();
  Logger.log("Index of new row: " + addedRowIdx);
  var range = sheet.getRange(1, 4, sheet.getMaxRows(), 1); // 4 == fourth column == column D
  var queryColumnFormulas = range.getFormulasR1C1();
  for (var i = 1; i < queryColumnFormulas.length; i++) {
    if (queryColumnFormulas[i][0] == "") {
      Logger.log("Inserting formula in row " + i);
      queryColumnFormulas[i][0] = queryColumnFormulas[i - 1][0]; // Copy formula from previous row
    }
  range.setFormulasR1C1(queryColumnFormulas);
  }
}

To install this script, in your spreadsheet click Tools -> Script editor, and paste the code above. In the script editor, click Resources -> Current project's triggers. select the function onFormSubmit, and the event From spreadsheet: On form submit.

Feel free to look at the example spreadsheet and corresponding form I setup.

Improve this answer
3
  • Vlad, THANK YOU! Quick question though. I copied your code and followed your instructions and it did not work originally. I actually had to change one value for it to work. In the code, var range = sheet.getRange(1, 3, sheet.getMaxRows(), 1); // 3 == third column I had to change the 3 to a 4 for it to work. Do you know why? Thanks again!
    – Mr. B
    Commented Nov 14, 2013 at 12:42
  • 1
    Great to hear you got it working! Oh yeah, the 3 should refer to column C. I see you have your formulas in column D instead, so 4 would be correct. I'll update my answer to reflect that. BTW, it's well worth spending some time learning Google Apps Scripts - they are really powerful, and allow you to do really interesting things with your spreadsheets.
    – Vidar S. Ramdal
    Commented Nov 14, 2013 at 12:46
  • 2
    Vlad, that is what I'm attempting to do, but I'm just getting started. I'm a high school teacher in San Diego and I'm attempting to learn GAS so I can build solutions that will work with Google Apps to improve the productivity of my school's staff (basically automate tasks). I'm doing this with a group of students as part of a <CODER> club (our website - grossmontgeeks.com). I'm trying to teach them to be 'independent learners' through the use of help forums and tutorials. Its exciting, its powerful, and its easier because of helpful people like you. Thanks again!
    – Mr. B
    Commented Nov 14, 2013 at 12:52
3

The following formula will auto-expand on new form entries.

Formula

=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))

Example

Please see the following answer I gave: https://webapps.stackexchange.com/a/52050/29140

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.