1
  1. I have a Form Builder Sheet where I have added Questions, and their Options using App Script.
  2. Added a Triger that updated every day.
  3. I have set this form Builder sheet, due to change of questions every day or can be changed as per need like Questions values and options.

I have a Database for Storing the Form responses, then use Pivot table to create the report.

There is a dropdown which is used to select the period for reporting, So I am selecting the period, and click create the report on new sheet and copy the selected data from Pivot table to new sheet.

So problem is that I want to update the Pivot Table Source range by using App Script not manually, as new responses will be added to Database Pivot table range will be dynamically updated for both rows and columns.

I have tried all the methods Like:

  1. Use dynamic Named Range using Offset Formula.
  2. Use Appscript.
  3. Use ChatGPT 3, 3.5, 4.

Nothing is wokring, is there any possible way to get arround this problem.

AL SADAT
  • 11
  • 1
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `So problem is that I want to update the Pivot Table Source range by using App Script not manually, as new responses will be added to Database Pivot table range will be dynamically updated for both rows and columns.`. Can I ask you about the detail of it? First, I would like to correctly understand your question. – Tanaike May 16 '23 at 12:17
  • Yes, You are right, I need help about pivot table dynamic range. – AL SADAT May 17 '23 at 13:49
  • So problem is that I want to update the Pivot Table Source range by using App Script not manually, as new responses will be added to Database Pivot table range will be dynamically updated for both rows and columns. – AL SADAT May 18 '23 at 07:35
  • Thank you for replying. I apologize for my poor English skill. Unfortunately, I cannot imagine `So problem is that I want to update the Pivot Table Source range by using App Script not manually, as new responses will be added to Database Pivot table range will be dynamically updated for both rows and columns.`. So, I cannot still think of a solution. I apologize for this. – Tanaike May 18 '23 at 07:43
  • May I clarify something... You have a Google form that exports submissions to a Google spreadsheet. From time-to-time, you add/delete questions to the form; the effect is that the number of columns in the linked spreadsheet keeps expanding to reflect new questions. You have a Pivot Table based on a given range; that time period is driven by a dropdown cell. Question: is your problem that the Pivot Table Source range doesn't include the columns for any new questions? – Tedinoz May 19 '23 at 01:35
  • To **create** a Pivot table, one uses `createPivotTable(sourceData)` [Doc ref](https://developers.google.com/apps-script/reference/spreadsheet/range#createpivottablesourcedata). My understanding is that there is no equivalent method to **update** the Source Data. The solution is probably to delete the given Pivot Table and create a new Pivot Table using a dynamically calculated range for `sourceData`. – Tedinoz May 19 '23 at 02:35
  • So is it possible to delete older one by script and create new one by script? So creating pivot table which has more than 26 Questions or columns will be a nightmare and also set the row Group, column Group and values Group is also a challenge. So anyone has idea to create all the stuff by script, we just need to click the button and all will happens by script in few moments. – AL SADAT May 19 '23 at 08:32
  • ` is it possible to delete older one by script` Yes, use `remove` [Doc ref](https://developers.google.com/apps-script/reference/spreadsheet/pivot-table#remove). `create new one by script` Yes, use `createPivotTable(sourceData)` [Doc ref](https://developers.google.com/apps-script/reference/spreadsheet/range#createpivottablesourcedata). `more than 26 Questions or columns will be a nightmare` Why? `set the row Group, column Group and values Group is also a challenge` Why? `anyone has idea to create all the stuff by script` Have you tried or researched this; have you tried to solve this yourself? – Tedinoz May 20 '23 at 02:39
  • `idea to create all the stuff by script` Yes, but depends on _your_ form, questions, spreadsheet response sheet and Pivot Table parameters. To date you haven't provided any specific information about these. To develop a solution, please provide example of form, example of Pivot Table, example of response spreadsheet including data and including how "active" and "inactive" questions can be identified.) – Tedinoz May 20 '23 at 03:29

0 Answers0