Creating dashboard from Multiple Google Sheet

[Rahullookernew]

Hi Community,

I have some basic knowledge of Lookerstudio, 

I have a sales report for 4 different regions( E.g A, B, C, D) in a Google Sheet And each region has its own month-wise Google Sheets. One google Sheet is for ONE month 

More detail

A - is having a monthly sales report in Google Sheets e.g 12 Different sheet for 12 months and Each spread Sheet is having sales Day wise

So can you please help how should I create a dashoboard in which when I select a month or date it should give figures accordingly of all region at once. I dont have to invdivdiually go to each sheet and see the report.

[ekramer]

To resolve this, you'll need join all these sheets into one data source (where an extra column for Region).  If I understand your description, you have twelve sheets per region and four regions, so 48 sheets of data.  Hopefully, the all have the same format (i.e. same columns) so all you have to do is merge the data from all 48 sheets into data source for Looker Studio to draw its reporting.

You could use the importrange() function 48 times to import the data into a single accumulator sheet, but this technique requires you to limit the expected rows from each source sheet.  E.g. if each source sheet (i.e. A Jan, A Feb... B Jan, B Feb, etc.) has an expected row count of no more than 200 rows, then your accumulator sheet would have the importrange() function for each consecutive sheet at rows 2, 202, 402, 602, 802, etc. up to 9602.  If any source sheet went over the expected row count, the accumulator would generate errors since the ranges would overlap and you'd lose importrange data from the source files.

A safer bet would be to push this data into BigQuery where you can perform joins.

[Rahullookernew]

I dont want to merge these sheets into one. cant this be done in looker studio?,

Also I tried , something in looker studio, I used drop down, but How to have 5 restaurant names in the dropdown?

PS; I have restaurant column in all the sheet and its all rows filled with that region till last date

[steff]

using a blend of multiple datasources (your spreadsheets) is not easy to work with and slows down data studio. A single data source is always better regarding report performance.

When merging the sheets, you don't have to overwrite any of your existing sheets, just create a new one which is in your case (and my opinion) the best way. But you have different options on how to do it.

The importrange() is an option, but I do not like the patchwork that comes with it. Use Google appsscript instead to do the task: create a new sheet out off all existing ones and schedule this operation (daily, hourly, whatever). The script can be created with AI and some patience 😉 

[Rahullookernew]

i dont want to merge all spread sheet into one spreadsheet, 

as each region has its own multiple sheets within for a particular month,.

Eg, Suppose for region A for month MAY its spreadsheet will have 3 to 4 sheets with in,

that is why I dont want to merge into one sheet, where it could be more complicated for me

rather then let them stay where they are and if I can add one hidden column in all sheet With column name as REGION and fill its rows with its column name,

Wouldnt that be convenient ?. please help me in this regard

[steff]

thats not what i meant.

you can keep the original sheets as you like to work with and to look at, but additionally automatically create (via appsscript) a a data source out of it (into an new sheet).

It doesn't matter how many original spreadsheets there are and how many sheets each of them has, and you won't need to even look at it 🙂

If you insist to keep the original format of your sheets, thats the best way in my opinion.