0

I would like to add a script to a button I created in Google Sheets. I would like the button to add a new column before my "Month Loss" column. I would like the new column to copy the formulas and formatting of the column to the left.

This will provide an easy way to track my body measurements and help to determine calculate my body fat percentage.

Here is a link to my sheet: https://docs.google.com/spreadsheets/d/1DP0SQpU16YEfPWoE8IRknoqQWu0wrFagzhLflVOkn8M/edit?usp=sharing

user3042658
  • 1
  • 1
  • 1
  • 2
    If you allow me to make a suggestion... Don't go that way with scripts copying columns and formulas... I suggest you create a google form where you put all your measurements, then you can use a dynamic table to get your data well shown, or even normal formulas. Have you tried the google forms summary of answers? Perhaps that can help. – Mauricio Moraes May 23 '16 at 17:53
  • 1
    also, give it a try first using spreadsheetApp, let us know where it goes wrong or you get stuck. – Zig Mandel May 23 '16 at 21:25
  • That is a great idea! Would I just add the dynamic table to another sheet on the responses page? – user3042658 May 24 '16 at 10:34
  • I have created a form and a pivot table for the responses. The pivot table doesn't automatically update when a new response is submitted. It looks like there used to be an option for the pivot table to "refresh" automatically or manually. I cannot find that option anymore and the only way I can update is by creating a new pivot table. Any ideas? – user3042658 May 24 '16 at 13:19

1 Answers1

0

A Pivot Table has been created though script. if you try to programmatically update the range, there is no way either because named ranges need to be deleted then re-added which causes on the pivot. if you are adding the new row by script, do not append it to the end. instead keep a fake row at the end and insert the new row just before it. your range (named or not) will update.

Even for the google forms case you might get it to work by pre-adding all blank rows to the response sheet and make the named range include the blank ones too.

Found this Stack Overflow ticket discuss about Pivot Table, you may also check the solution offered by the community: Google Spreadsheet Pivot Table Range Update Using Scripts

Community
  • 1
  • 1
Android Enthusiast
  • 4,826
  • 2
  • 15
  • 30