New answers tagged

2

It doesn't work with arrayformula - importdata, importxml, and split will not work it unfortunately.


1

Since the content of L7 is going to be overwritten, spreadsheet formulas cannot perform such reset. You need a script that has the spreadsheet keys of all the spreadsheets to be changed, and has the authority to edit them (i.e., the Google account under which it runs has this authority). It can be either a stand-alone script, or bound to your master ...


3

You need =E$1 there. (Or, =$E$1 if the formatted range spans multiple columns.) Putting E1 would compare cells to the string "E1", which is not what you want. Putting =E1 compares the first cell in your column range to E1, the second to E2, the third to E3, etc -- because the reference is relative. The dollar sign makes it an absolute reference.


1

Your draft is pretty good, but there is a design flaw: if you insert the values between B and C, then the column with formulas will become D. So, next time the script will try to get data, it will be looking at a wrong place. Simply put, the source of the data you are recording (i.e., the column with formulas) should stay in the same place. You can put the ...


3

Short answer Add a new sheet. Add the following formulas to the new sheet Cell A2 -> Headers =OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0) This could be repeated several times, one for each header column, just change the last parameter accordingly. Cell B2 -> data =OFFSET('Form responses ...


2

The where clause supports several types of string matching. Method 1: String comparison with >= and < The query string select A where A < 'G' selects all strings that would precede G in a dictionary, i.e., all that begin with letters A-F. This is case-sensitive. The case-insensitive form is select A where lower(A) < 'g' More complex ...



Top 50 recent answers are included