Google-sheets – Automatically adjust corresponding cells when using “=query”

google sheets

I just learned how to use the =query function in Google Sheets, pulling information from one sheet to the next when a specific condition is met.

=query('Sheet1'!A:Z, "select B, D, E, F, G, I, L where A = 'WON'",0)

Example:

Sheet 1:

A AUG  WON
B SEPT PENDING
C SEPT LOST
D OCT  WON

Sheet 2:

A AUG
D OCT

In the second sheet, I add information in the columns after the ones =query pulled in, for example:

A AUG DANNY
D OCT NICKY

But when I change the source sheet, from PENDING to WON, it becomes this:

A AUG  DANNY
B SEPT NICKY
D OCT

How do I fix it so whatever information I put in the same row adjusts with the cells it was originally put with, leaving empty cells I can fill out beside the new entries?

Best Answer

The values added to the right of QUERY() result aren't "tied" to the source range.

To "tie" them, add these values to the source sheet, as was suggested in a comment posted by @pnuts.

Short answer

Instead of using IMPORTRANGE use Google Apps Script

Explanation

According to Change a spreadsheet's locale, time zone, recalculation, and language the recalculation interval for IMPORTRANGE is 30 mins. If shorter recalculation interval is needed then somehow the formula parameters should be changed.

Unfortunately, in the new Google Sheets is not possible to use NOW, RAND or RANDBETWEEN functions, so the alternative is to use a Google Apps Script.

Explanation about the use of non-deterministic functions

In the thread provided in the comment by Jacob Jan Tuinstra it's mentioned the use of an extra URL parameter that it's updated at certain intervals. That thread is from 2011.

The core idea was to add to the URL something like "&workaround="&NOW() and set the recalculation setting to on change and every minute but this could be too frequent as the import task could take more than one minute, so instead of NOW() you should round it to a good enough interval, let's say 10 minutes, then instead of NOW() use

INT(NOW()/TIME(0;10;0))

Let name this formulas time_interval.

The cell formula containing IMPORTRANGE will look like this

=IMPORTRANGE(
  "https://docs.google.com/spreadsheets/d/spredsheet_key&workaround="&time_interval,
  "Sheet1!A1:AB"
 )

Unfortunately the above formula returns the following error

Error
This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()

The same happens with the other import functions (IMPORTDATA, IMPORTHTML, IMPORTFEED, IMPORTXML)

References

Extend Google Docs, Sheets, and Forms with Apps Script

Google Sheets Query for Non-Empty Cells

Please try:

=QUERY('Form Responses 1'!A:N,"SELECT C,E,G,H where G is not null or H is not null order by C,E")

Best Answer

Related Solutions

Google-sheets – Delay in updating referenced cells when using IMPORTRANGE

Short answer

Explanation

Explanation about the use of non-deterministic functions

References

Google Sheets Query for Non-Empty Cells

Related Topic