14

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been ...


11

May be an old question but its still being looked up - so here is the workaround: Importrange as we all know will only import raw un formatted data into a new googlesheet. As a workaround to avoid the hassle of redoing all the conditional formatting rules on the new import data sheet, do the following: - on the original sheet which you plan to import data ...


7

I was having a similar problem too. Try splitting up your import range with an array formula. Example: Importrange("sheet", "A:ZZ") becomes ARRAYFORMULA({Importrange("sheet", "A:ZZ5000");Importrange("sheet", "A5001:ZZ10000")...etc...}) Test this with different data sizes to get the shortest version you can and it should do what you need.


7

One way to do this is with ArrayFormula. If the sheet you want to import from is named Sheet1 and you want to select the entirety of column A, you can do the following: =ArrayFormula('Sheet1'!A:A) You can see an example of this in action here: https://docs.google.com/spreadsheets/d/1kJraNDzrLF8-W_BxbL8pU6e_QuoMqFcl_sgyzT14L2Y/edit?usp=sharing In general ...


6

Short answer QUERY select argument can't merge columns. Explanation The QUERY built-in function uses Google Visualization API Query Language. It doesn't include a concatenate operator. One alternative is to concatenate the data. Examples Assume that First Name and Last Name columns are columns A and B respectively. Example 1 Add an auxiliary column to ...


6

This was an issue with the sharing settings on the sheets. It looks like a sheet that is set to Specific People in the sharing settings cannot be automatically imported into a second sheet. If you have access to the first sheet, you can set up an IMPORTRANGE, but it will not update. The sharing permissions on the first sheet need to be set in such a way ...


5

The following comes from Jimmy in this Web Apps answer. In both spreadsheets insert an =now() equation in a random cell, say Z1 In both spreadsheets insert an =importrange() function that references the now function of the other spreadsheet. Go into your spreadsheet settings and choose to recalculate on every minute. I tried a lot of other suggestions ...


4

I found this syntax using Insert -> Define new range RANGE FOR REFERENCING OTHER SPREADSHEET: Whole A column: 'Spreadsheet With Spaces in the name'!A:A Whole B column: 'Spreadsheet With Spaces in the name'!B:B etc. Then you can use it as: =COUNTIF('First Page'!B:B, "valueToMatch")


4

I found the easiest way was to put a simple if statement around the importrange. B1 = Sheet ID B2 = Sheet Name and Range B3 = Now() =if (B3> now()-1, IMPORTRANGE(B1,B2),) This works every time.


4

Yes, you can bring the two together like so: =filter(IMPORTRANGE("URL","A:J"),INDEX(IMPORTRANGE("URL","A:J"),0,3)="Yes")


4

In certain cases you can use Google array literal representation to achieve a join. =query({Sheet1!A:B, Sheet2!C:D}, "select Col1, Col3 where Col2 = Col4") This works only when the number of rows in both tables is same.


4

Short answer Is there a script, or some other solution, that will auto-Allow Access in a spreadsheet? The only solution is that the owner or any editor do the mandatory authorization for each of the external spreadsheets. Explanation Google Apps Script doesn't include any class or method for Google Sheets built-in functions, so the authorization should ...


4

I just ran into this same issue...we've never had an issue with importrange working across MANY sheets which are selectively shared, but today it randomly stopped working. I ran upon this article in my research. Upon further investigation, I found that only one spreadsheet that was failing to export--the importrange functions on that sheet were still ...


4

If your referring to the cell where the regular text is you don't need to add the additional "" around the ="""2016 Data!"&B41&"2:"&B41&"13""" just simply enter: ="2016 Data!"&B41&"2:"&B41&"13" You only add the additional quotes when your directly entering that data into the formula, not when its via the cell reference


4

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))


3

The datestamp workaround doesn't seem to work for me - there's an explicit note that using datestamps and rand functions aren't allowed. My very hacky workaround is to delete the cell and then hit ctrl+z. It forces a refresh every time. It's trivial to script this onopen, or at specific time intervals to keep the data fresh.


3

I would use the following formula. Formula =QUERY( IMPORTRANGE( "0Anrm0HgYyzzRdGR3TWpBelFCenJYM01ZUDZsaWNsTkE", "Sheet1!C3:C13" ), "SELECT Col1 WHERE Col1 <> ''" ) Copy/paste =QUERY(IMPORTRANGE("0Anrm0HgYyzzRdGR3TWpBelFCenJYM01ZUDZsaWNsTkE", "Sheet1!C3:C13"),"SELECT Col1 WHERE Col1 <> ''") Explained The QUERY formula ...


3

No, it's not possible to "skip columns". Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following. First formula (A1): =QUERY( IMPORTRANGE("URL","Sheet Name!A2:P"), "SELECT Col1,Col2,Col3 WHERE Col11 CONTAINS 'West'", 1 ) Second formula (F1): =QUERY( ...


3

It's not about the way you shared; it's about the locale that you use for each spreadsheet. Go to File -> Spreadsheet Setting -> and make sure you have same Locale and Time zone for both Google sheets. The one you put your data and calculations, and the one you shared with the others, and you used importrange() on it.


3

IMPORTRANGE requires authorization to access an external spreadsheet, but the authorization pop-up only is shown when IMPORTRANGE is not nested inside of other functions. Once you make the authorization, IMPORTRANGE could be nested inside other functions like QUERY. On the other hand, your formula includes a cell reference inside the quotes of the SQL ...


3

The date provided to QUERY must be: a string of format yyyy-mm-dd Try changing from =date '"&$AR3&"'"; 0))) to =date '"&TEXT($AR3;"yyyy-mm-dd")&"'"; 0)))


2

Short answer You are right, the import functions updates their result at certain intervals. Explanation From Change a spreadsheet's locale, time zone, recalculation, and language, a help article from the official documentation, Note: External data functions recalculate at the following intervals: ImportRange: 30 minutes ImportHtml, ImportFeed,...


2

If you want to leave the data on the IMPORT sheet untouched, then you need to do this: =SUM(IMPORTRANGE("key","range")) This is a good post, here on Web Applications, about the IMPORTRANGE function: IMPORTRANGE


2

There are two things that you need to take into consideration. Consideration The IMPORTRANGE function requires two parameters (strings): spreadsheet key data range The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure. ...


Only top voted, non community-wiki answers of a minimum length are eligible