Google sheets integration removes custom calcuations

  • 21 March 2023
  • 3 replies

Userlevel 1
Badge +1

Hi, When running a new Google Sheets Add-on Import, custom calculations in custom fields deleted. 

Is there anyway to avoid this? 




Best answer by alex 21 March 2023, 16:09

View original

3 replies

Userlevel 5
Badge +3

Yes, you need to create a separate page in the sheet that references the columns from the import page via a ={Sheet1A1:A} or if another Google Sheet entirely, IMPORTRANGE().

The import page will always totally overwrite everything, so any addition of formulas or calculations will be wiped out. So you need to build essentially a new sheet with all of the actively imported columns from the original page, and there you can add in columns that are formulas and such as they won't be overridden.

Does this data change a lot? Genuinely sounds like you'd be better off with something like Airtable and a custom integration that pushes the data over as it changes in Copper, with all of the additional formulas and calculations updated back into Copper even if needed.

Although this could be overkill, it totally depends on the use-case here.

Give the import sheet page formula mapping thing a try in Google Sheets to see if that works for you 👌

Userlevel 1
Badge +1

Thanks @alex 

Keeping calculations on a separate sheet is a good tip : )

Userlevel 7
Badge +7

@triches I agree with @alex’s suggestion! The import sheet itself is best used as a reference sheet, and building a separate sheet that references it allows you to refresh the underlying data without having to set up all your formulas again.

I’ve also done it in the same sheet as the import, but using macros to automatically fill all my formulas each time I refresh the data. But, putting it in a different sheet would be more resilient for any changes you might need to do in the future.