Repairing broken workbook links in Migrated Files

When workbooks are migrated from Google Workspace to Microsoft 365, formulas that import data from other workbooks often break. This could happen because of changes in file paths, broken links to Google Drive from SharePoint/OneDrive, or differences in how Google Sheets and Excel handle external data references. Google Sheets uses the `ImportRange` formula, which is incompatible with Excel, leading to errors when files are migrated.

Excel can identify broken `ImportRange` links in migrated files and will guide you through the process of repairing them.

Important: Migration source ID mapping

After the migration, the Google file links will change to SharePoint URLs. This change will cause the cross-file link references in Excel formulas to break. However, the Excel app can automatically fix these links if a managed property named MigrationSourceID is set to the Google Doc ID. 

To fix broken workbook links, the formula’s migration source IDs need to be mapped during migration.

Note:

1. If migration is carried out through MMP platform, the source ID mapping is supported by default
2. If migration is carried out through 3^rd party tools, then it’s advised to handle the source id mapping in the 3^rd party tool as described in the in the article: Mapping Google Source IDs in the Migration Spreadsheet to Resolve Broken Links Post-Migration.

Example functions for linking to other workbooks

Formula in Google Sheets:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")

Formula after migration to Excel:

=IFERROR(@__xludf.DUMMYFUNCTION("IMPORTRANGE(""https://docs.google.com/spreadsheets/d//abcd123abcd123", "sheet1!A1:C10"")"),"Formula")

Formula after workbook compatibility:

=IFERROR(@__xludf.UNSUPPORTED(IMPORTRANGE(' https://docs.google.com/spreadsheets/d/[abcd123abcd123]sheet1'!A1:C10)),"Formula")

How to repair broken links

The broken links can be repaired through one of the two approaches mentioned below.

Approach 1 – recommended

In the guided pane select the option to fix the broken link and follow the process as below

Step 1: Identify the broken workbook links in the "Update broken workbook link" section and select the update link option on the presented card.

Step 2: In the "Change source" option, select the correct file and click Select.

Step 3: On selecting, the correct file links will populate, and the content in the grid will refresh where that link is present.

Approach 2

1. Update the link to the relevant workbook from Sharepoint/OneDrive:

   Format =[SourceWorkbook.xlsx]Sheet1!$A$1:$A$10

   Example:

   'https://microsoft-my.sharepoint.com/personal/v-mathavale_microsoft_com/Documents/[data.xlsx]Sheet1'!$A$6:$A$9

   ​​​​​​Learn more about managing workbook links: Manage workbook links - Microsoft Support

2. Remove all the additional wrapping in the formulas (marked in bold), such as the iferror, importrange and unsupported functions in the formulas:

   ='https://microsoft-my.sharepoint.com/personal/v-mathavale_microsoft_com/Documents/[data.xlsx]Sheet1'!$A$6:$A$9)),"Formula")

3. Clear any cells with spilled values, except the cell containing the external link.

Clear all the cells within the blue boundary, except the cell containing the external link.	The cells will update with refreshed content.

Reference Links

1. Manage workbook links - Microsoft Support
2. Create workbook links - Microsoft Support