title: get all rows in a sheet with data where a specific column is empty using Google Apps Scripts
body: <p>Our team has a Google Form we use for intake. Once an intake is submitted I have a GAS script that will process it (create some files in Google Drive, send some e-mails, etc...).</p>
  
  <p>Instead of triggering on the <strong>on form submit</strong> event of the Spreadsheet linked to the Form, I am using an hourly trigger. There is a specific reason for this that is out-of-scope for this question but I can explain if needed.</p>
  
  <p>The Spreadsheet linked to the Form will eventually have thousands of rows. </p>
  
  <p>My script will go through each unprocessed row. I added a column on the Spreadsheet to track which rows have been processed. When a row is processed some string is added to column A. For unprocessed rows, column A will be empty.</p>
  
  <p>I am trying to find the fastest and most efficient way to get all the unprocessed rows -- rows where column A is blank.</p>
  
  <p>Initial thoughts:</p>
  
  <ul>
  <li>Use <code>Sheet.getDataRange().getValues()</code> to get all of the rows, iterate through each, and process the unprocessed ones. Then use the index of the row to mark the row processed on the Spreadsheet. This way would work but after the Spreadsheet has thousands of rows this way will get really slow.</li>
  <li>Apply a filter to the Spreadsheet and filter on rows where column A is blank. I can apply the filter but then there doesn't seem to be an easy way to get all the visible rows and their row number so I can mark them processed.</li>
  </ul>
  
  <p>So then I figured out I could use the <code>query</code> function on a second sheet to show all rows where column A is empty and then I could use that sheet in my script for what needs to be processed.</p>
  
  <pre><code>=query('Form Responses'!A:V, "SELECT * WHERE A IS NULL")
  </code></pre>
  
  <p>But this does not include the row number from the <code>Form Responses</code> sheet so I wouldn't know which row to go back to on <code>Form Responses</code> to mark processed.</p>
  
  <p>I was able to add the row number using <code>arrayformula</code> like this:</p>
  
  <pre><code>=query({arrayformula(row('Form Responses'!A:V)), 'Form Responses'!A:V}, "SELECT * WHERE Col2 IS NULL")
  </code></pre>
  
  <p>But this adds a bunch of rows with numbers, even if the source table doesn't have data in that row.</p>
  
  <p>And if I don't include the row number it still wont work cause if I call <code>Sheet.getDataRange()</code> on the sheet that has the <code>query</code> function, it returns more rows than there is data. </p>
  
  <p>I made an example sheet - <a href="https://docs.google.com/spreadsheets/d/1LYpqfj3clZiaGCPgp_eRfrxhQKWambRyQn3uNCFAIpU/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1LYpqfj3clZiaGCPgp_eRfrxhQKWambRyQn3uNCFAIpU/edit?usp=sharing</a>.</p>
  
  <p>So is there a fast/efficient way to get all rows with data in a sheet where a specific column is empty? Either using <code>query</code> or some other way?</p>