CODEWITHSUNDEEP
google-sheets
Juraj Ondrej H.
Juraj Ondrej H.

Reputation: 23

How to add column with fixed value to imported data Google spreadsheet

I need to merge data from two spreadsheets and add column with the file ID on each row.

Spreadsheet 1 Columns => A B C D

Spreadsheet 2 Columns => O P Q R

Spreadsheet merged Columns => 'X' A B C D O P Q R

edited: where column X contains ID of spreadsheet.

...where 'X' is not column from Spreadsheet 1 or Spreadsheet 2 but it is new column in merged new spreadsheet and contains text/ID of spreadsheet where is origin of the row.

I use now this code...

=ARRAYFORMULA({
  QUERY(importrange("spreadsheet1-ID"; "'Sheet'!A:D");"SELECT * WHERE Col1!=''");
  QUERY(importrange("spreadsheet2-ID"; "'Sheet'!O:R");"SELECT * WHERE Col1!=''")
})

...and I get:

A1 B1 C1 D1
A2 B2 C2 D2
O1 P1 Q1 R1
O2 P2 Q2 R2

But I need:

spreadsheet1-ID A1 B1 C1 D1
spreadsheet1-ID A2 B2 C2 D2
spreadsheet2-ID O1 P1 Q1 R1
spreadsheet2-ID O2 P2 Q2 R2

Can I use something like this to add fixed column with ID in QUERY?

=ARRAYFORMULA({
  QUERY(importrange("spreadsheet1-ID"; "'Sheet'!A:D");"SELECT 'ID1', * WHERE Col1!=''");
  QUERY(importrange("spreadsheet2-ID"; "'Sheet'!O:R");"SELECT 'ID1', * WHERE Col1!=''")
})

Upvotes: 2

Views: 7830

Answers (1)

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

={QUERY(importrange("spreadsheet1-ID", "'Sheet'!A:X"),"SELECT Col24,Col1,Col2,Col3,Col4 WHERE Col1!=''");QUERY(importrange("spreadsheet2-ID", "'Sheet'!O:X"),"SELECT Col10,Col1,Col2,Col3,Col4 WHERE Col1!=''")}

To add an ID that does not exist in the sheets themselves, try this formula:

=ArrayFormula({IF(SIGN(row(INDIRECT("1:"&ROWS(QUERY(importrange("key1", "'Sheet'!A:D"),"WHERE Col1!=''"))))),"ID1",),QUERY(importrange("key1", "'Sheet'!A:D"),"WHERE Col1!=''");IF(SIGN(row(INDIRECT("1:"&ROWS(QUERY(importrange("key2", "'Sheet'!O:R"),"WHERE Col1!=''"))))),"ID2",),QUERY(importrange("key2", "'Sheet'!O:R"),"WHERE Col1!=''")})

You can see it working in this example sheet: https://docs.google.com/spreadsheets/d/1sSHP0sekUc5PxnubpnieWthD2H_9s4P2JWqmtxM906o/edit?usp=sharing

Upvotes: 3

Related Questions