Class GoogleSheets

Provides an interface for syncing localization data to a Google Sheet.

Inheritance

Object

GoogleSheets

Namespace: UnityEditor.Localization.Plugins.Google

Syntax

public class GoogleSheets

Constructors

GoogleSheets(IGoogleSheetsService)

Creates a new instance of a GoogleSheets connection.

Declaration

public GoogleSheets(IGoogleSheetsService provider)

Parameters

Type	Name	Description
IGoogleSheetsService	provider	The Google Sheets service provider. See SheetsServiceProvider for a default implementation.

Properties

SheetsService

The sheets provider is responsible for providing the SheetsService and configuring the type of access. SheetsServiceProvider.

Declaration

public IGoogleSheetsService SheetsService { get; }

Property Value

Type	Description
IGoogleSheetsService

SpreadSheetId

The Id of the Google Sheet. This can be found by examining the url: https://docs.google.com/spreadsheets/d/SpreadsheetId/edit#gid=SheetId Further information can be found here.

Declaration

public string SpreadSheetId { get; set; }

Property Value

Type	Description
String

UsingApiKey

Is an API key being used or is it an OAuth?

Declaration

protected virtual bool UsingApiKey { get; }

Property Value

Type	Description
Boolean

Methods

AddSheet(String, NewSheetProperties)

Creates a new sheet within the Spreadsheet with the id SpreadSheetId.

Declaration

public int AddSheet(string title, NewSheetProperties newSheetProperties)

Parameters

Type	Name	Description
String	title	The title for the new sheet
NewSheetProperties	newSheetProperties	The settings to apply to the new sheet.

Returns

Type	Description
Int32	The new sheet id.

CreateSpreadsheet(String, String, NewSheetProperties, ITaskReporter)

Creates a new Google Spreadsheet.

Declaration

public (string spreadSheetId, int sheetId) CreateSpreadsheet(string spreadSheetTitle, string sheetTitle, NewSheetProperties newSheetProperties, ITaskReporter reporter = null)

Parameters

Type	Name	Description
String	spreadSheetTitle	The title of the Spreadsheet.
String	sheetTitle	The title of the sheet(tab) that is part of the Spreadsheet.
NewSheetProperties	newSheetProperties
ITaskReporter	reporter	Optional reporter to display the progress and status of the task.

Returns

Type	Description
(T1, T2)<String, Int32>	Returns the new Spreadsheet and sheet id.

ExecuteRequest<TResponse, TClientServiceRequest>(TClientServiceRequest)

Declaration

protected virtual TResponse ExecuteRequest<TResponse, TClientServiceRequest>(TClientServiceRequest req)
    where TClientServiceRequest : ClientServiceRequest<TResponse>

Parameters

Type	Name	Description
TClientServiceRequest	req

Returns

Type	Description
TResponse

Type Parameters

Name	Description
TResponse
TClientServiceRequest

ExecuteRequestAsync<TResponse, TClientServiceRequest>(TClientServiceRequest)

Declaration

protected virtual Task<TResponse> ExecuteRequestAsync<TResponse, TClientServiceRequest>(TClientServiceRequest req)
    where TClientServiceRequest : ClientServiceRequest<TResponse>

Parameters

Type	Name	Description
TClientServiceRequest	req

Returns

Type	Description
Task<TResponse>

Type Parameters

Name	Description
TResponse
TClientServiceRequest

GetColumnTitles(Int32)

Returns all the column titles(values from the first row) for the selected sheet inside of the Spreadsheet with id SpreadSheetId. This method requires the SheetsService to use OAuth authorization as it uses a data filter which reuires elevated authorization.

Declaration

public IList<string> GetColumnTitles(int sheetId)

Parameters

Type	Name	Description
Int32	sheetId	The sheet id.

Returns

Type	Description
IList<String>	All the

GetRowCount(Int32)

Returns the total number of rows in the sheet inside of the Spreadsheet with id SpreadSheetId. This method requires the SheetsService to use OAuth authorization as it uses a data filter which reuires elevated authorization.

Declaration

public int GetRowCount(int sheetId)

Parameters

Type	Name	Description
Int32	sheetId	The sheet to get the row count from.

Returns

Type	Description
Int32	The row count for the sheet.

GetRowCountAsync(Int32)

Asynchronous version of GetRowCount(Int32)

Declaration

public async Task<int> GetRowCountAsync(int sheetId)

Parameters

Type	Name	Description
Int32	sheetId	The sheet to get the row count from

Returns

Type	Description
Task<Int32>	The row count for the sheet.

GetSheets()

Returns a list of all the sheets in the Spreadsheet with the id SpreadSheetId.

Declaration

public List<(string name, int id)> GetSheets()

Returns

Type	Description
List<(T1, T2)<String, Int32>>	The sheets names and id's.

OpenSheetInBrowser(String)

Opens the spreadsheet in a browser.

Declaration

public static void OpenSheetInBrowser(string spreadSheetId)

Parameters

Type	Name	Description
String	spreadSheetId

OpenSheetInBrowser(String, Int32)

Opens the spreadsheet with the sheet selected in a browser.

Declaration

public static void OpenSheetInBrowser(string spreadSheetId, int sheetId)

Parameters

Type	Name	Description
String	spreadSheetId
Int32	sheetId

PullIntoStringTableCollection(Int32, StringTableCollection, IList<SheetColumn>, Boolean, ITaskReporter, Boolean)

Pulls data from the Spreadsheet with id SpreadSheetId and uses columnMapping to populate the collection.

Declaration

public void PullIntoStringTableCollection(int sheetId, StringTableCollection collection, IList<SheetColumn> columnMapping, bool removeMissingEntries = false, ITaskReporter reporter = null, bool createUndo = false)

Parameters

Type	Name	Description
Int32	sheetId	The sheet(Spreadsheet tab) to pull the data from.
StringTableCollection	collection	The collection to insert the data into.
IList<SheetColumn>	columnMapping	The column mappings control what data to extract for each column of the sheet. The list must contain one IPullKeyColumn.
Boolean	removeMissingEntries	After a pull has completed, any keys that exist in the collection but did not exist in the sheet are considered missing, this may be because they have been deleted from the sheet. A value of true will remove these missing entries; false will preserve them.
ITaskReporter	reporter	Optional reporter to display the progress and status of the task.
Boolean	createUndo	Should an Undo be recorded so any changes can be reverted?

Examples

A StringTableCollection can exist over several Google Sheets, for example one per Locale. This example shows how to pull one of those Locales into a StringTableCollection.

[MenuItem("Localization/Google Sheets/Pull English")]
public static void PullEnglish()
{
    // Setup the connection to Google. You will need a preconfigured SheetsServiceProvider asset.
    var sheetServiceProvider = AssetDatabase.LoadAssetAtPath<SheetsServiceProvider>("Assets/Sheets Service Provider.asset");
    var googleSheets = new GoogleSheets(sheetServiceProvider);
    googleSheets.SpreadSheetId = "My spread sheet id"; // We need to provide the Spreadsheet id. This can be found in the url. See docs for further info.

    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");

    // We need configure what each column contains in the sheet
    var columnMappings = new SheetColumn[]
    {
        // Column A contains the Key
        new KeyColumn { Column = "A" },

        // Column B contains any shared comments. These are Comment Metadata in the Shared category.
        new KeyCommentColumn { Column = "B" },

        // Column C contains the English Locale and any comments that are just for this Locale.
        new LocaleColumn { Column = "C", LocaleIdentifier = "en", IncludeComments = true },
    };

    int mySheetId = 123456; // This it the id of the sheet in the Google Spreadsheet. it will be in the url after `gid=`.
    googleSheets.PullIntoStringTableCollection(mySheetId, tableCollection, columnMappings);
}

This example shows how to pull all the locales in your project by using the ColumnMapping to generate the column mapping data for you.

[MenuItem("Localization/Google Sheets/Pull Project Locales")]
public static void PullProjectLocales()
{
    // Setup the connection to Google. You will need a preconfigured SheetsServiceProvider asset.
    var sheetServiceProvider = AssetDatabase.LoadAssetAtPath<SheetsServiceProvider>("Assets/Sheets Service Provider.asset");
    var googleSheets = new GoogleSheets(sheetServiceProvider);
    googleSheets.SpreadSheetId = "My spread sheet id"; // We need to provide the Spreadsheet id. This can be found in the url. See docs for further info.

    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");

    // CreateDefaultMapping will create a KeyColumn and a LocaleColumn for each Locale in the project.
    // This assumes that the table was created and pushed to using the same column mappings.
    var columnMappings = ColumnMapping.CreateDefaultMapping();
    int mySheetId = 123456; // This it the id of the sheet in the Google Spreadsheet. it will be in the url after `gid=`.

    // Now pull.
    // removeMissingEntries will remove any Keys that we have in the String Table Collection that do not exist in the Pull update.
    // reporter is an optional reporter that can be used to povide feedback in the editor during the Pull.
    googleSheets.PullIntoStringTableCollection(mySheetId, tableCollection, columnMappings, removeMissingEntries: true, reporter: new ProgressBarReporter());
}

This example shows how to use the data that was configured in a Google Sheets extension to perform a pull.

[MenuItem("Localization/Google Sheets/Pull With Google Extension")]
public static void PullWithExtension()
{
    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");
    var googleExtension = tableCollection.Extensions.FirstOrDefault(e => e is GoogleSheetsExtension) as GoogleSheetsExtension;
    if (googleExtension == null)
    {
        Debug.LogError($"String Table Collection {tableCollection.TableCollectionName} Does not contain a Google Sheets Extension.");
        return;
    }

    PullExtension(googleExtension);
}

static void PullExtension(GoogleSheetsExtension googleExtension)
{
    // Setup the connection to Google
    var googleSheets = new GoogleSheets(googleExtension.SheetsServiceProvider);
    googleSheets.SpreadSheetId = googleExtension.SpreadsheetId;

    // Now update the collection. We can pass in an optional ProgressBarReporter so that we can updates in the Editor.
    googleSheets.PullIntoStringTableCollection(googleExtension.SheetId, googleExtension.TargetCollection as StringTableCollection, googleExtension.Columns, reporter: new ProgressBarReporter());
}

This example shows how to pull every StringTableCollection that contains a GoogleSheetsExtension.

[MenuItem("Localization/Google Sheets/Pull All Google Sheets Extensions")]
public static void PullAllExtensions()
{
    // Get every String Table Collection
    var stringTableCollections = LocalizationEditorSettings.GetStringTableCollections();

    foreach (var collection in stringTableCollections)
    {
        // Its possible a String Table Collection may have more than one GoogleSheetsExtension.
        // For example if each Locale we pushed/pulled from a different sheet.
        foreach (var extension in collection.Extensions)
        {
            if (extension is GoogleSheetsExtension googleExtension)
            {
                PullExtension(googleExtension);
            }
        }
    }
}

PushStringTableCollection(Int32, StringTableCollection, IList<SheetColumn>, ITaskReporter)

Extracts data from collection using columnMapping and sends it to the sheet inside of the Spreadsheet with id SpreadSheetId. This method requires the SheetsService to use OAuth authorization as an API Key does not have the ability to write to a sheet.

Declaration

public void PushStringTableCollection(int sheetId, StringTableCollection collection, IList<SheetColumn> columnMapping, ITaskReporter reporter = null)

Parameters

Type	Name	Description
Int32	sheetId	The sheet(Spreadsheet tab) to insert the data into.
StringTableCollection	collection	The collection to extract the data from.
IList<SheetColumn>	columnMapping	The column mappings control what data will be extracted for each column of the sheet. The list must contain 1 KeyColumn.
ITaskReporter	reporter	Optional reporter to display the progress and status of the task.

Examples

A StringTableCollection can exist over several Google Sheets, for example one per Locale. This example shows to push one of those Locales.

[MenuItem("Localization/Google Sheets/Push English")]
public static void PushEnglish()
{
    // Setup the connection to Google. You will need a preconfigured SheetsServiceProvider asset.
    var sheetServiceProvider = AssetDatabase.LoadAssetAtPath<SheetsServiceProvider>("Assets/Sheets Service Provider.asset");
    var googleSheets = new GoogleSheets(sheetServiceProvider);
    googleSheets.SpreadSheetId = "My spread sheet id"; // We need to provide the Spreadsheet id. This can be found in the url. See docs for further info.

    // Prepare the data we want to push.
    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");

    // We need configure what each column will contain in the sheet
    var columnMappings = new SheetColumn[]
    {
        // Column A will contain the Key
        new KeyColumn { Column = "A" },

        // Column B will contain any shared comments. These are Comment Metadata in the Shared category.
        new KeyCommentColumn { Column = "B" },

        // Column C will contain the English Locale and any comments that are just for this Locale.
        new LocaleColumn { Column = "C", LocaleIdentifier = "en", IncludeComments = true },
    };

    int mySheetId = 123456; // This it the id of the sheet in the Google Spreadsheet. it will be in the url after `gid=`.

    // Now send the update.
    googleSheets.PushStringTableCollection(mySheetId, tableCollection, columnMappings);
}

This example shows how to push all the locales in your project by using ColumnMapping to generate the column mapping data for you.

[MenuItem("Localization/Google Sheets/Push Project Locales")]
public static void PushProjectLocales()
{
    // Setup the connection to Google. You will need a preconfigured SheetsServiceProvider asset.
    var sheetServiceProvider = AssetDatabase.LoadAssetAtPath<SheetsServiceProvider>("Assets/Sheets Service Provider.asset");
    var googleSheets = new GoogleSheets(sheetServiceProvider);
    googleSheets.SpreadSheetId = "My spread sheet id"; // We need to provide the Spreadsheet id. This can be found in the url. See docs for further info.

    // Prepare the data we want to push.
    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");

    // CreateDefaultMapping will create a KeyColumn and a LocaleColumn for each Locale in the project.
    var columnMappings = ColumnMapping.CreateDefaultMapping();
    int mySheetId = 123456; // This it the id of the sheet in the Google Spreadsheet. it will be in the url after `gid=`.

    // Now send the update. We can pass in an optional ProgressBarReporter so that we can see updates in the Editor.
    googleSheets.PushStringTableCollection(mySheetId, tableCollection, columnMappings, new ProgressBarReporter());
}

This example shows how to use the data that was configured in the GoogleSheetsExtension to perform a push.

[MenuItem("Localization/Google Sheets/Push With Google Extension")]
public static void PushWithExtension()
{
    // You should provide your String Table Collection name here
    var tableCollection = LocalizationEditorSettings.GetStringTableCollection("My Strings");
    var googleExtension = tableCollection.Extensions.FirstOrDefault(e => e is GoogleSheetsExtension) as GoogleSheetsExtension;
    if (googleExtension == null)
    {
        Debug.LogError($"String Table Collection {tableCollection.TableCollectionName} Does not contain a Google Sheets Extension.");
        return;
    }

    PushExtension(googleExtension);
}

static void PushExtension(GoogleSheetsExtension googleExtension)
{
    // Setup the connection to Google
    var googleSheets = new GoogleSheets(googleExtension.SheetsServiceProvider);
    googleSheets.SpreadSheetId = googleExtension.SpreadsheetId;

    // Now send the update. We can pass in an optional ProgressBarReporter so that we can updates in the Editor.
    googleSheets.PushStringTableCollection(googleExtension.SheetId, googleExtension.TargetCollection as StringTableCollection, googleExtension.Columns, new ProgressBarReporter());
}

This example shows how to push every StringTableCollection that contains a GoogleSheetsExtension.

[MenuItem("Localization/Google Sheets/Push All Google Sheets Extensions")]
public static void PushAllExtensions()
{
    // Get every String Table Collection
    var stringTableCollections = LocalizationEditorSettings.GetStringTableCollections();

    foreach (var collection in stringTableCollections)
    {
        // Its possible a String Table Collection may have more than one GoogleSheetsExtension.
        // For example if each Locale we pushed/pulled from a different sheet.
        foreach (var extension in collection.Extensions)
        {
            if (extension is GoogleSheetsExtension googleExtension)
            {
                PushExtension(googleExtension);
            }
        }
    }
}

PushStringTableCollectionAsync(Int32, StringTableCollection, IList<SheetColumn>, ITaskReporter)

Asynchronous version of PushStringTableCollection(Int32, StringTableCollection, IList<SheetColumn>, ITaskReporter)

Declaration

public async Task PushStringTableCollectionAsync(int sheetId, StringTableCollection collection, IList<SheetColumn> columnMapping, ITaskReporter reporter = null)

Parameters

Type	Name	Description
Int32	sheetId	The sheet(Spreadsheet tab) to insert the data into.
StringTableCollection	collection	The collection to extract the data from.
IList<SheetColumn>	columnMapping	The column mappings control what data will be extracted for each column of the sheet. The list must contain 1 KeyColumn.
ITaskReporter	reporter	Optional reporter to display the progress and status of the task.

Returns

Type	Description
Task

SendBatchUpdateRequest(String, Request[])

Declaration

protected virtual BatchUpdateSpreadsheetResponse SendBatchUpdateRequest(string spreadsheetId, params Request[] requests)

Parameters

Type	Name	Description
String	spreadsheetId
Request[]	requests

Returns

Type	Description
BatchUpdateSpreadsheetResponse

SendBatchUpdateRequest(String, IList<Request>)

Declaration

protected virtual BatchUpdateSpreadsheetResponse SendBatchUpdateRequest(string spreadsheetId, IList<Request> requests)

Parameters

Type	Name	Description
String	spreadsheetId
IList<Request>	requests

Returns

Type	Description
BatchUpdateSpreadsheetResponse

SendBatchUpdateRequestAsync(String, IList<Request>)

Declaration

protected virtual Task<BatchUpdateSpreadsheetResponse> SendBatchUpdateRequestAsync(string spreadsheetId, IList<Request> requests)

Parameters

Type	Name	Description
String	spreadsheetId
IList<Request>	requests

Returns

Type	Description
Task<BatchUpdateSpreadsheetResponse>