Load google spreadsheets with a cycle

[Fq]

Hi

Let's say I have different google spreadsheets files for different projects, they contain the same fields but different values.

I want to load them all, creating only one table.

I was thinking creating a config file like this:

And then when loading files from GSS (I already have configurated the web connector, the only thing I need is the Google Spreadsheet ID).

Creating a loop,

So...

Load

*,

$(ProjectName) AS Project

From ......$(FileID)...

And using the code only once, but looping over the values of the config table above.

Currently, I am loading separately each file and concatenating them, but I don't think is optimal.

Do you have any ideas in mind?

[rachel_delany · Accepted solution]

Yes this is doable.

Do something like this:

ConfigFile:

LOAD ProjectName, FileID FROM ConfigFile;

For each file in FieldValueList('FileID')

    TempProject:

    LOAD ProjectName

    Resident ConfigFile

    WHERE FileID = '$(file)';

   LET project = peek('ProjectName',1,'TempProject');

   ProjectData:

  Load

   *,

    '$(project)' AS Project

    From '$(file)';

    next;