Map Google source IDs to repair workbook links after migration

Applies To
Excel Web App

When Google Sheets files are migrated to SharePoint or OneDrive, formulas that refer to other Google files can contain links that no longer resolve. Excel for the web can repair these workbook links when the destination file is associated with the original Google file ID.

This article is for SharePoint administrators and migration tool developers. It explains how to store the original Google file ID in a SharePoint text column named MigrationSourceID. If your Microsoft migration tooling already maps source IDs, you don't need to perform these steps. When you use a third-party migration tool, configure the tool to provision and populate this column.

Important

The internal column name must be MigrationSourceID. Store only the source file ID, not the full Google URL. For example, for https://docs.google.com/spreadsheets/d/1AbCDefGhIJkLmN/edit, store 1AbCDefGhIJkLmN.

Before you begin

  • Identify the SharePoint site and document library that will contain the migrated files.
  • Record the Google file ID for each source file.
  • Make sure that your migration application has permission to create site columns and update items in the destination library.

Step 1: Create the MigrationSourceID site column

Create a single-line text site column with the internal name MigrationSourceID, and then add the site column to each destination document library. Creating a site column rather than a library-only column allows SharePoint Search to generate the crawled and managed properties that Excel uses to locate the migrated file.

You can create the site column in SharePoint or by using Microsoft Graph. The following request creates the column by using Microsoft Graph:

POST https://graph.microsoft.com/v1.0/sites/{site-id}/columns
Content-Type: application/json

{
  "description": "Original Google file ID used to repair links after migration",
  "enforceUniqueValues": false,
  "hidden": false,
  "indexed": false,
  "name": "MigrationSourceID",
  "text": {
    "allowMultipleLines": false,
    "appendChangesToExistingText": false,
    "linesForEditing": 1,
    "maxLength": 255
  }
}

The least-privileged delegated or application permission for this request is Sites.Manage.All. For request and permission details, see Create a columnDefinition in a site.

After you create the column, add it to the destination document library. Verify that its internal name is MigrationSourceID; changing only its display name isn't sufficient.

Step 2: Store the source ID on each migrated file

Populate MigrationSourceID for every destination file that can be referenced by a cross-workbook formula. The value must be the ID of that file in Google Drive.

Use the SharePoint Migration API

When you use the SharePoint Migration API, include MigrationSourceID and its value as item metadata in the migration package's Manifest.xml. Use the internal field name MigrationSourceID and the source Google file ID as its value.

Manifest.xml is the primary metadata descriptor in a SharePoint Migration API package. Follow the constrained SharePoint Content Migration schema rather than adding unrecognized elements to the manifest. For package structure and schema requirements, see:

Use Microsoft Graph

Files in a SharePoint document library are also represented as list items. Update the list item's fields after the file is created:

PATCH https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items/{item-id}/fields
Content-Type: application/json

{
  "MigrationSourceID": "1AbCDefGhIJkLmN"
}

The least-privileged delegated or application permission for this request is Sites.ReadWrite.All. For request and permission details, see Update listItem.

Step 3: Verify search indexing

After the populated site column is crawled, SharePoint automatically generates a crawled property, a managed property, and a mapping between them. The automatically generated managed property can include a type suffix. Excel requires a managed property named or aliased MigrationSourceID.

In the SharePoint search schema, find the crawled property for the MigrationSourceID site column. If its managed property isn't named or aliased MigrationSourceID, create a text managed property with that name and map the crawled property to it. Property names are case-sensitive.

If the property or a recently stamped value doesn't appear:

  1. Confirm that the site column was added to the destination library before values were stored.
  2. Confirm that each destination file's list item contains the expected MigrationSourceID value.
  3. Request that SharePoint reindex the document library.
  4. Check the search schema again after the content has been crawled.

For more information about automatically generated properties, mappings, and reindexing, see Manage the search schema in SharePoint.

Troubleshooting

Issue Resolution
The update request returns 400 Bad Request. Confirm that the column is attached to the destination library and that the JSON property uses the internal name MigrationSourceID.
The update request returns 403 Forbidden. Confirm that the application or signed-in user has the required Microsoft Graph permission and access to the destination site.
The stored value contains a Google URL. Replace it with only the file ID from the URL.
Excel doesn't find the destination file. Confirm the source ID value and search-schema mapping, and then reindex the library. Search updates aren't immediate.
Only some migrated links can be repaired. Make sure every source workbook referenced by another workbook has a corresponding destination file with MigrationSourceID populated.

After the files are indexed, users can repair their links in Excel for the web. For the user workflow, see Repair broken workbook links in migrated files.