In the Google Sheets Add-on, avoid and resolve conflicts you may encounter when you send and refresh a read-write connection.

Send and refresh enables you to update Anaplan with changes you make in Google Sheets.

If there have been no changes to the data in Anaplan and no changes to cell format in Google Sheets, you can simply submit your data. However, where there are conflicts in data between Anaplan and Google Sheets, you may need to exit the Send and refresh wizard to resolve the conflicts.

You can resolve value change conflicts within the send and refresh process, as long as there are no other conflicts. Where there's a value change for a cell in Google Sheets, and a value change for the same cell in Anaplan, you can:

  • apply your changes to Anaplan, or
  • refresh all data to update Google Sheets with the values and cell formats used in Anaplan.

However, if any other conflicts are present, you'll need to click Cancel to exit the wizard and resolve the conflicts, then try again.

This conflict occurs if you enter a value for a cell in Google Sheets that cannot be entered into the cell in Anaplan because the type of value entered is incompatible with the cell format.

For example, if you enter a non-numerical text value for a cell that in an Anaplan has a number format, the value is incompatible. This can happen if the format changes in Anaplan between the previous connection and the Send and refresh.

If you change the format for the cell in Google Sheets and enter a value that is not valid for the format the cell had at the previous connection, you can try to Apply Anaplan format in the wizard. In some cases, this resolves the conflict.

The table below displays the cell formats where Apply Anaplan format may resolve the conflict.

Google Sheets format at previous connectionGoogle Sheets current formatAnaplan current format
NumberPercentageNumber
NumberCurrencyNumber
NumberPlain text (when the text is a number written as a string)Number
CurrencyPercentageCurrency
PercentageCurrencyPercentage

If you change the format in your worksheet to any other data format and change the value, you cannot submit data to Anaplan. Either Refresh all data to replace all values and formats in the worksheet with data from Anaplan, or Cancel.

If you only want to change the Google Sheets format for the cell or cells with format conflicts:

  1. In Google Sheets, go to Format > Number, and select a compatible format (see table above) for the cell or cells in conflict.
  2. Perform the Send and refresh procedure again.
    As long as there are no other conflicts, the Send and refresh should be successful.

To resolve this conflict, if you want to retain the Google Sheets data format for the cell or cells in conflict:

  1. Ask a workspace administrator to change the format for the cell or cells in Anaplan to match the format used in Google Sheets.
  2. Perform the Send and refresh procedure again.
    As long as there are no other conflicts, the Send and refresh should be successful.

This occurs when you enter a new value for a cell in Google Sheets when the cell is read-only in Anaplan.

Note that if you only edit read-only cells in Google Sheets, this does not register as a change at the Compare data stage of Send and refresh. The add-on only compares values it can submit with the previous connection at this stage. The values for cells that are editable in Anaplan display as blue in your worksheet. The values for cells that are read-only in Anaplan display as black in your worksheet.

You can edit values that display as black in your Google Sheets worksheet, but the changes cannot be submitted to the read-only cells in Anaplan.

When you try to submit value changes, any changes you have made to cells in your worksheet that are read-only in Anaplan display as conflicts in the Conflicts table.

The conflict may also occur if the cell in Anaplan was read-write at the previous connection, but is now read-only. Cells may be read-only if they contain a formula, are affected by Breakback, or if you do not have read-write access to the cell.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

You cannot edit data where there's no corresponding line item in Anaplan. This conflict can occur when:

  • the item the cell references has been deleted in Anaplan, or
  • you no longer have access to the cell in Anaplan.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

You may not have access to this cell, or the item the cell references has been deleted from Anaplan.  

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

This conflict occurs when you change the value for a cell in Google Sheets and the cell type at the previous connection has no data. 

The add-on supports the following cell data types:

  • Boolean
  • Date
  • List
  • Number
  • Text
  • Time period

If the cell in the previous connection to Anaplan had the type no data, the add-on detects this conflict at the Compare data stage. If the cell has changed in Anaplan since the previous connection, the conflict is detected as A dimension or dimension item for this cell is not recognized.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

In most cases, if a cell type is unsupported, this is detected at the Compare data stage of Send and refresh. At that stage, the add-on compares your changes to the data at the previous connection.

However, the add-on checks your data against the live data in Anaplan when you click Submit. This conflict is detected when you submit your data, if the cell type in Anaplan has been changed to an unsupported format since the previous connection.

The add-on supports the following cell data types:

  • Boolean
  • Date
  • List
  • Number
  • Text
  • Time period

If the cell in Anaplan is formatted as no data, the add-on cannot submit your data to that cell.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

In Anaplan, where a line item has a list format, you can only select values from the list for that line item. If your worksheet connects to a model with a list-formatted line item, you can enter any value to the corresponding cell in Google Sheets. However, if the value you enter is not in the list with which the line item is formatted, you cannot update Anaplan with the Google Sheets value.

This conflict occurs when a value has been deleted from the list with which the line item is formatted since the previous connection. The cell in Google Sheets is unchanged since the previous connection, but you cannot submit the data to Anaplan as the list-formatted line item can only accept values that currently exist in the list.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

In Anaplan, where a line item has a list format, you can only select values from the list for that line item. If your worksheet connects to a model with a list-formatted line item, you can enter any value to the corresponding cell in Google Sheets. However, if the value you enter is not in the list with which the line item is formatted, you cannot update Anaplan with the Google Sheets value.

This conflict occurs when you change the value for the cell in Google Sheets, but the new value is not an item in the list used to format the corresponding line item.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

A change in value or format has been detected, but the add-on does not recognize the type of conflict.

Refresh all data to update the worksheet with Anaplan data, or contact your workspace administrator. 

If the Brought-Forward configuration for a line item is changed in a module, and you then click Refresh in the Google Sheets Add-on, the change is only applied after you've signed out and in again.

If you do not interact with the Google Sheets Add-on for more than 30 minutes you'll be automatically signed out. The next time you sign in, the change will be applied.