Hello!

I need a little assistance automating beeminder data. I am trying to make a google sheet that gets data from Beeminder via IFTTT but I am having trouble figuring out how to make it work properly.

This is for a highly modified version of the 12 Week Year and we’re trying to make it nearly 100% automated and cheat-proof. I imagine some are familiar with the 12WY but if not, it’s essentially setting bigger goals that are broken down into smaller “tactics” (e.g. “lose 10 pounds” is a goal and “cardio 30 minutes a day” is a tactic) over the course of 12 weeks. The purpose of the sheet is to get the number of tactics completed/total intended for that tactic per week, but I am running into issues with the IFTTT applet.

I am trying to automate it so data is not counted for tactics that are time-based if the time is not met. For example, for the goal “do cardio for 30 minutes a day,” the data should not be counted if a person only does 20 minutes. I have accomplished this using =IF(logical_expression, value_if_true, value_if_false). So if someone does 20 minutes, the value for the cell with this equation will be 0; if they do 34 minutes, for example, it will be 1. Then I am using those 0s and 1s to calculate the total execution score by dividing the sum of all the 0s and 1s by the total number of 1s possible for the week. The problem that I am running into is that once the data from beeminder is sent to sheets, the equations that I have in my equation cells (that are separate from the cells I have the data sent to) gets shifted down one. For example, the function “=if(B15>20,1,0)” changes to “=if(B16>20,1,0)” once the data is sent to row 15. Is there any way to force sheets to keep it on a particular cell? Maybe Airtable would be a better option!

Thanks!!