1

I am trying to import several ranges from 8 different spreadsheets in to one master list.

This is my first Sheet.

This is my second Sheet.

This is my master list Sheet.

For now, I am testing it on just 2 spreadsheets. Will add the remaining once I figure this out.

So on my master list sheet, A4, I have this code

=query(
{
importrange("https://docs.google.com/spreadsheets/d/1scqPKhWpbZeOK_iYR7EaypNxMhh__V_O2FLUjbjrNFc/edit#gid=0","Scholarship Applicants Database!A4:W");
importrange("https://docs.google.com/spreadsheets/d/1PkBWxUG_0BG4gNhXFGHInSdVI5nMo4FReRpCQIVVs3w/edit#gid=664164578","Scholarship Applicants Database!A4:W")
},"Select * where Col1 <> ''")

Which works well. Then on Column X, these code does not work.

=query(
{
importrange("https://docs.google.com/spreadsheets/d/1scqPKhWpbZeOK_iYR7EaypNxMhh__V_O2FLUjbjrNFc/edit#gid=0","CDP Scoring Sheet (Initial)!G4:J");
importrange("https://docs.google.com/spreadsheets/d/1PkBWxUG_0BG4gNhXFGHInSdVI5nMo4FReRpCQIVVs3w/edit#gid=664164578","CDP Scoring Sheet (Initial)!G4:J")
},"select * where A = '"&$A4&"'")

What I am trying to do here is, import the ranges from Sheet1 and 2, from this sheet (CDP Scoring Sheet (Initial)), Column G4 to J, with the condition that Column A of Sheet 1 and 2 matches Column A of my Master list. Error I am receiving is "Unable to parse query string for function QUERY parameter 2: NO_COLUMN: A", I then change the "A" on the "where" part to "Col1", I get the error "Query complete with an empty output" and the result of "#N/A".

I also thought of changing my importrange from "G4:J" to A4:J" but I know it's not right cause it will then pull out the data from columns A to F which I no longer need.

Help please.

Kelvs
  • 97
  • 2
  • 14

1 Answers1

1

try:

=QUERY(
{
importrange("1scqPKhWpbZeOK_iYR7EaypNxMhh__V_O2FLUjbjrNFc", "CDP Scoring Sheet (Initial)!A4:J");
importrange("1PkBWxUG_0BG4gNhXFGHInSdVI5nMo4FReRpCQIVVs3w", "CDP Scoring Sheet (Initial)!A4:J")
},
 "select Col7,Col8,Col9,Col10 
  where Col1 = '"&$A4&"'")
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hello, It worked but the result is only for the sheet 2. I've made changes on the link so you can access them. [ https://imgur.com/a/SL7q1s2 ] (Image of sample sheet) the highlighted yellow on X24 should also show some results. – Kelvs Aug 02 '21 at 06:37
  • @Kelvs do you want to sum the output from multiple sheet imputs? – player0 Aug 02 '21 at 08:01
  • 1
    Ah no Sir. I figured it out, what I did was I dragged down the code to every cell below it, and it works. So, I then tried to add all the remaining spreadsheets, and have this code now [ https://imgur.com/a/MIC0FZC ]. However, an error occurs saying "Array_Literal" so I followed your answer here "https://stackoverflow.com/questions/58136900/how-to-fix-error-in-array-literal-an-array-literal-was-missing-values-for-one-o". The error disappeared but nothing is happening on my sheet. All the cells are blank now. – Kelvs Aug 02 '21 at 08:26
  • @Kelvs make sure you run each importrange formula separately in your sheet and clicking on allow access button. after all sheets are connected only then you can use your query formula – player0 Aug 02 '21 at 08:30
  • All other sheets are similar to each other except for the master list since all I did was to "Make A Copy" of them. FYI. Thank you for your help so far! – Kelvs Aug 02 '21 at 08:30
  • 1
    I allowed access to all sheets. Working fine now. Thank you very much! Appreciate your help bud! – Kelvs Aug 02 '21 at 08:46