Google sheet connector breaks pivots on update

[soncoku]

Greetings everyone.
I have a report that I update daily.

The output of this report goes to Google Sheet using the connector.
Since a lot of data can change in a day my best option for this report was to select the  "overwrite" google sheet data every time the report is updated.

Now my problem starts here. Every time the data is overwritten in the google sheet (sheet1), the pivot that I've made in (sheet2) appears blank. Even if I choose the new range for the pivot again I still have to manually add all the rows, columns, values, filters. 

Is there any way I can solve this problem? 
Logically I know that when the data is overwritten in sheet1, google sheets automatically breaks the pivot in sheet 2 since it's not the same data anymore and everything sheet2 (the pivot) knew about sheet 1 has been overwritten, but I still need a solution.

Does anybody have an idea what could be done in this case. 

Important
Appending the sheet is not an option because the data I have changes all the time.

[danilang]

Hi @soncoku 

i've never used the Google Sheets connector before, but a way to deal with a similar issue in Excel is to define a named range  on the sheet and then write to the range.  Since the entire sheet isn't erased and recreated, the links in the pivot table should continue working. 

Dan

[soncoku]

@danilang 
I don't think it is the same for google sheets but however I found the solution

[soncoku · Accepted solution]

I found the solution that works for me.

I use the google sheet connector to get the data from Alteryx to Google sheets. SHEET1

1) Copy paste the headers of the columns (meaning the first row) A1, to a different sheet, SHEET2, A1

2) Then on the second row of SHEET2 , A2, put the formula google sheet formula IMPORTRANGE to import the data from SHEET1, range A2 and below (we skip first row so we don't get the headers again)

3) Create the PIVOT from SHEET2 and in the range give it A1:X (assuming that x is the last column you have data in).

This assures that you PIVOT stays untouched whenever you OVERWRITE the google sheet.