App sheet formula values in spreadsheet

[Ben_Rix]

Hi Everyone,

Hope you are all well

I have a new column added to my spreadsheet, I regenerate the structure in app sheet. I have then given the column an App Formula. How do I then sync the original row values from that app formula to the spreadsheet.

Regards
Ben

[Aleksi · Accepted solution]

Hi Ben, you would need to open and save all records one by one. If you have lot of records, it would be easier to create a temporary arrayformula for that calculation and then copy/paste them as values.

[Ben_Rix]

Hey Aleksi,

I thought that was the case, Thats exactly what I have done in the past, I was hoping that id missed something and I could avoid doing another temp formula.

Regards
Ben

[Koichi_Tsuji]

Automate this task. Try this.

Add one more physical column to your spreadsheet. Say [Refresh]. At the same time add value of 0 (zero) to the spreadsheet for the existing rows.

Regenrate the table.

Create action to increment this number.

Action to change value with expression [Refresh] + 1

Hide this action.

Set new action of Execute Action to the set of rows. For the reference rows, then apply to pick up ALL THE ROW of this referenced and target table.

Then hire this action.

You will see the [Refresh] field will be 1. At the same time, the app formula you place into your field will be reevaluated and insert the value as a result of expression.

It takes a bit of time App complete all the action based on the number of existing rows, but it will be automated.

@Aleksi

[Ben_Rix]

Thanks for this, I will definitely give this a shot because the formula was a bit complicated to set up in the first place and trying to figure it out for the spreadsheet will more than likely take more time than setting up this automation.

[Ben_Rix]

Hey Tsuji,

I dont quite understand this step

“Set new action of Execute Action to the set of rows. For the reference rows, then apply to pick up ALL THE ROW of this referenced and target table.”

Could you please elaborate a little.

[Koichi_Tsuji]

[Ben_Rix]

Thanks

[Koichi_Tsuji]

The point here is Referenced Row.

As I demonstrated as example, pass the most simplest select expression TableName[ID] to select all the rows in the target table to get the action applied in bulk.

It is equivalent with

Select(YourTable[Id], True)

[Ben_Rix]

Thanks for the tsuji, its definaltey doing something, ive got around 9000 rows and the fans are wizzing haha

[Koichi_Tsuji]

Depending on your time you do this job.

In the middle of the day, enjoy a cup of tea or coffee, while the appsheet does the jobs.

In the evening, enjoy (a) bottle of beer(s) until she finished it for you.

no rush.

Otherwise you need to open up form view and save, save save save. for 9000 times. It is upto you, but you select that option, then you might book clinic beforehands to cure your hands.

[Ben_Rix]

Bottle of beer it is

Thanks so much

[Koichi_Tsuji]

Once you finished it. then delete the [Refresh] column from spreadsheet and regenerate the table.