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

Lu_zhang
Lu_zhang Member
in Datasets

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

image.png

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?

image.png

Best Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    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()

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • JasonAltenburg
    JasonAltenburg Coach
    Answer ✓

    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
    david_cunningham Coach
    edited May 7 Answer ✓

    @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

    Screen Shot 2024-05-06 at 10.43.42 PM.png
    Screen Shot 2024-05-06 at 10.44.37 PM.png
    Screen Shot 2024-05-06 at 10.44.58 PM.png

    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.

    Screen Shot 2024-05-06 at 10.46.44 PM.png

    Hope this is helpful!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    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()

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • JasonAltenburg
    JasonAltenburg Coach
    Answer ✓

    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
    Lu_zhang Member
    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
    david_cunningham Coach
    edited May 7 Answer ✓

    @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

    Screen Shot 2024-05-06 at 10.43.42 PM.png
    Screen Shot 2024-05-06 at 10.44.37 PM.png
    Screen Shot 2024-05-06 at 10.44.58 PM.png

    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.

    Screen Shot 2024-05-06 at 10.46.44 PM.png

    Hope this is helpful!

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Lu_zhang
    Lu_zhang Member

    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!

Categories