Is it possible to upload many google sheets at the same time?

Lu_zhang

I have 30 sheets under a google sheet workbook. Can I upload them(30 sheets) in one time?

Now via Google Sheets Connector, I can select only 1 sheet each time. I have the whole year data waiting to be uploaded, It is really too many manual workload.

Is it possible to upload many google sheets at the same time?

david_cunningham

With the Google sheets connector, you have to specify which sheet you want to upload, so unfortunately there isn't a built in way to do this.

Here is what I would recommend

Assuming these sheets all have the same columns. Which I'm guessing is the case since you're wanting to stack them. Combine all your sheets together in Google Sheets or Excel, and then upload that combined file to Domo. This would be much faster than uploading each individual sheet to Domo.

To do this, you can use the QUERY() function in Google Sheets alongside ArrayFormula()

JasonAltenburg

Agree with @david_cunningham here, and wanted to point out even if the columns weren't the same, nothing stopping you from making your dataset super-wide and splitting the columns in MagicETL. In other words, you could put your tables side-by-side up to 18,278 columns of data then import.

david_cunningham

@Lu_zhang sure, happy to help. For an example, let's say you have the following 3 sheets, all with the same column headers.

Sheet1, Sheet2, Sheet3

You can use the following formula to combine all 3

=UNIQUE(QUERY({Sheet1!A1:C;Sheet3!A1:C},"WHERE Col1 IS NOT NULL ORDER BY Col1 ASC"))

You would adjust with your sheet names and columns. In this case there are 3 columns, so I go to Column C.

Unique makes sure that there are no duplicate rows, and also allows you to pull in the headers only once. It's important that there is a primary key in your dataset when taking this approach. In this example, I'm assuming that Col1 is the primary key (hence the NOT NULL check).

The formula that you see pulls together all 3 sheets into one.

Hope this is helpful!

david_cunningham

With the Google sheets connector, you have to specify which sheet you want to upload, so unfortunately there isn't a built in way to do this.

Here is what I would recommend

Assuming these sheets all have the same columns. Which I'm guessing is the case since you're wanting to stack them. Combine all your sheets together in Google Sheets or Excel, and then upload that combined file to Domo. This would be much faster than uploading each individual sheet to Domo.

To do this, you can use the QUERY() function in Google Sheets alongside ArrayFormula()

JasonAltenburg

Agree with @david_cunningham here, and wanted to point out even if the columns weren't the same, nothing stopping you from making your dataset super-wide and splitting the columns in MagicETL. In other words, you could put your tables side-by-side up to 18,278 columns of data then import.

Lu_zhang

https://community-forums.domo.com/main/discussion/comment/95414#Comment_95414

@david_cunningham Thank you for the enlightment. Can you show me how to design QUERY() function in Google Sheets alongside ArrayFormula()? This way looks automatic with small manual workload, but I failed to design it.

david_cunningham

@Lu_zhang sure, happy to help. For an example, let's say you have the following 3 sheets, all with the same column headers.

Sheet1, Sheet2, Sheet3

You can use the following formula to combine all 3

=UNIQUE(QUERY({Sheet1!A1:C;Sheet3!A1:C},"WHERE Col1 IS NOT NULL ORDER BY Col1 ASC"))

You would adjust with your sheet names and columns. In this case there are 3 columns, so I go to Column C.

Unique makes sure that there are no duplicate rows, and also allows you to pull in the headers only once. It's important that there is a primary key in your dataset when taking this approach. In this example, I'm assuming that Col1 is the primary key (hence the NOT NULL check).

The formula that you see pulls together all 3 sheets into one.

Hope this is helpful!

Lu_zhang

https://community-forums.domo.com/main/discussion/comment/95588#Comment_95588

@david_cunningham I've tried this approach and I am being shocked. It is so great way! VERY FAST! thank you!