Import several GSheet files with one script

[JuMo]

Hello

I have several files with exactly the same structure (each file correspond to the sales of one country). I would like to import all of them with the same script. Is it possible ?

My script:

LOAD 

Country as Country, 

[Order Number] as [Order Number], 

[Order Date] as [Order Date],  

Status as Status, 

[Product Family] as [Product Family], 

Product as Product, 

Volume as Volume, 

[Unit Price] as [Unit Price], 

Price as Price;

SELECT 

Country,

[Order Number],

[Order Date],

Status,

[Product Family],

Product,

Volume,

[Unit Price],

Price

FROM GetSheetValues

WITH PROPERTIES (

spreadsheetKey='XXXXXXX',

range='Sells!A:I',

valueRenderOption='FORMATTED_VALUE',

dateTimeRenderOption='FORMATTED_STRING',

generatedNumberedColumns='false',

skipRows=''

);

Is it possible to loop over XXXXXXX ??

[JuMo]

No one inspired ? 😄

[Kushal_Chawda]

@JuMo  If you know the exact name of sheets in advance, you can definitely use loop

[JuMo]

Yes, I definitly know the name of the files ... But I can also use Loop with GSheet ?

[Kushal_Chawda · Accepted solution]

@JuMo  I haven't try but give it a try

List_of_sheets:
LOAD * Inline [
sheet_key
sheet1
sheet2 ];

Data:
Load * Inline [
Junk ];

for Each vSheet in FieldValueList('sheet_key')

Concatenate(Data)
LOAD
Country as Country,
[Order Number] as [Order Number],
[Order Date] as [Order Date],
Status as Status,
[Product Family] as [Product Family],
Product as Product,
Volume as Volume,
[Unit Price] as [Unit Price],
Price as Price;

SELECT
Country,
[Order Number],
[Order Date],
Status,
[Product Family],
Product,
Volume,
[Unit Price],
Price
FROM GetSheetValues
WITH PROPERTIES (
spreadsheetKey='$(vSheet)',
range='Sells!A:I',
valueRenderOption='FORMATTED_VALUE',
dateTimeRenderOption='FORMATTED_STRING',
generatedNumberedColumns='false',
skipRows=''
);

Next

Drop Table List_of_sheets; Drop Field Junk;

[JuMo]

Many thanks... It works perfectly ... I would never had come with this part by myself

Data:
Load * Inline [
Junk ];

[Kushal_Chawda]

@JuMo  That part is not mandatory in your case as your structure is same. But in case you are loading data with LOAD * and other sheets might have additional column then this is necessary because due to slight difference in structure autoconcatenation of data will not work