googlesheettranslate 2.11.35

Non credential proved url scrapper for google sheet

Overview

sheet-localization generates Android / iOS localization files from single Google Spreadsheet.

Input supports:

• CVS
• CSV
• Excel
• GoogleSheetAPI
• GoogleSheet URL

Output supports:

• JSON file general for JS i18n
• python i18n JSON
• Android XML file

Localization

This is a python script that downloads localization strings and CI color information from a public Google Sheet and generates resource files for iOS and Android projects.

Installation

pip3.8 install googlesheettranslate

Prerequisites

Create a Google Sheet document with sheets like these:

Publish the sheeet to the web by pressing File -> Publish to the web. Select Whole Document and Website. This gives the script access to the public JSON API of Google Docs.

Find the ID of your document by copying it from your browsers address bar.

Usage

Create a data_sync.config.json in your project and populate it with the contents of the example config. Replace the sheetId with your own. You can modify the other settings to fit the needs of your project.

Then open a terminal at your project path and run l10n_sync. Look for your newly created resource files.

You can also copy data_sync.py to your project and then run python3 data_sync.py when your config file is ready.

Example

Have a look into the Output folder for some example files generated by using the provided configuration file.

Command line parameters

You generally run sheet-localization like this:

python /path/to/sheet-localization/main.py /path/to/account_credentials.json SpreadSheetName TargetName

1. account_credentials.json is a file you get after registering Google service account

2. SpreadSheetName is the name of the spreadsheet to open

   Note: Google service account must be given read permission to access the spreadsheet by sharing the document with the service account's email

3. TargetName can be one of android, ios, or ios-swift.

Targets

android target

sheet-localization generates res/value-<lang>/strings.xml hierarchy in the current directory.

ios target

sheet-localization generates <lang>.lproj/Localizable.strings hierarchy in the current directory.

The script also generates LocalizationConstants.h, LocalizationConstants.m files with translation constants.

ios-swift target

sheet-localization generates <lang>.lproj/Localizable.strings hierarchy in the current directory.

The script also generates LocalizationConstants.swift files with translation constants (actually, enum).

Installation

The scripts require Python 2.7+.

iOS generation will not work in Python 3+

1. Linux TODO

2. Windows TODO

3. macOS

   1. Upgrade six package

      sudo easy_install -U six

   2. Install pip package manager

      sudo easy_install pip

   3. Install gspread and oauth2client packages

      sudo -H pip install --upgrade gspread oauth2client

Step by step guide

1. Clone sample Google spreadsheet

Clone sample spreadsheet to your Google Drive.

The sample document contains two mandatory pages:

1. SRC contains translations and keys
2. CFG contains configurations where keys are located and what row translations start at

These pages are required by the script, so make sure to keep them.

2. Create new Google API project

Go to Google API console and create a new project.

3. Enable Google Drive API

Enable Google Drive API.

4. Create service account

Create service account credentials for a Web server to access Application data

Name the service account and give it Project -> Editor role.

Upon account creation you should get a special JSON, which contains all necessary credentials.

5. Allow service account to read the spreadsheet document

The JSON you downloaded looks like this:

{
  "type": "service_account",
  "project_id": "localization-173405",
  "private_key_id": "d37cdb95af7f817a05c",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCO8ACoDvAG0q8R\xg3bQzHYCVrEDBcBFkfJ4d8dfy9FdIS++p3XvmLOWnFyMreQTPh1\njmx7jdmDpEwZHNZrj2dYYf0Xta8A0wxdejqUmNq4CyOBqTzomqCdzu36qBp8szUk\nN1l9G9u+rLcm9J/BlinOeA==\n-----END PRIVATE KEY-----\n",
  "client_email": "[e-mail]",
  "client_id": "1016040",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/localization%40localization-173405.iam.gserviceaccount.com"
}

To allow the service account to read your spreadsheet, you need to give client email read permissions to your document. You can do it in sharing settings.

6. Generate Android localization files

Go to the directory of your choice and generate Android localization files with the following commands:

cd /path/to/android/project

python /path/to/sheet-localization/main.py /path/to/service_account_credentials.json 'Spreadsheet Key' android

This creates res/values-<lang>/strings.xml files with contents like this:

<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="history">History</string>
<string name="notifications">Notifications</string>
<string name="favorite_places">Favourite places</string>
<string name="about">About</string>
<string name="wait_map_tag">wait_map_tag</string>
<string name="main_title_order"></string>
<string name="order_detail_title"></string>
<string name="action_im_here">I'm here</string>
<string name="action_get_order">Order</string>
</resources>

7. Generate iOS localization files

Go to the directory of your choice and generate iOS localization files with the following commands:

cd /path/to/android/project

python /path/to/sheet-localization/main.py /path/to/service_account_credentials.json 'Spreadsheet Key' ios

This creates

1. <lang>.lproj/Localizable.strings files with contents like this:

   "Menu.Item.History" = "History";
   "Menu.Item.Notifications" = "Notifications";
   "Menu.Item.Favourites" = "Favourite places";
   "Menu.Item.About" = "About";
   "Profile.Email.Title" = "Email";
   "Profile.BirthDate.Title" = "Birth date";
   "Profile.ChangeAvatar.Title" = "Another photo";
   "Order.Address.Here" = "I'm here";
   "Details.Order" = "Order";
   

2. LocalizationConstants.h file with contents:

   /*!
   * @b en@: History
   
   * @b ru@: История
   
   * @b uk@: Історія
   
   */
   extern NSString * const trMenuItemHistory;
   
   /*!
   * @b en@: Notifications
   
   * @b ru@: Уведомления
   
   * @b uk@: Сповіщення
   
   */
   extern NSString * const trMenuItemNotifications;
   

3. LocalizationConstants.m file with contents:

   import "LocalizableConstants.h"
   NSString * const trMenuItemHistory = @"Menu.Item.History";
   NSString * const trMenuItemNotifications = @"Menu.Item.Notifications";
   NSString * const trMenuItemFavourites = @"Menu.Item.Favourites";
   NSString * const trMenuItemAbout = @"Menu.Item.About";
   NSString * const trProfilePhoneTitle = @"Profile.Phone.Title";
   NSString * const trProfileEmailTitle = @"Profile.Email.Title";
   

Localization constants help prevent string typos.

8. Generate iOS localization files for Swift

If you use ios-swift target, you get single LocalizationConstants.swift, which looks like this:

import Foundation

enum L10n {
    case MenuItemHistory
    case MenuItemNotifications
    case MenuItemFavourites
    case MenuItemAbout
}


extension L10n: CustomStringConvertible {
    var description: String { return self.string }

    var string: String {
        switch self {
            case .MenuItemHistory:
                return L10n.tr(key: "Menu.Item.History")
            case .MenuItemNotifications:
                return L10n.tr(key: "Menu.Item.Notifications")
            case .MenuItemFavourites:
                return L10n.tr(key: "Menu.Item.Favourites")
            case .MenuItemAbout:
                return L10n.tr(key: "Menu.Item.About")
        }
    }

    private static func tr(key: String, _ args: CVarArg...) -> String {
        let format = NSLocalizedString(key, bundle: Bundle(for: BundleToken.self), comment: "")
        return String(format: format, locale: Locale.current, arguments: args)
    }
}

func tr(_ key: L10n) -> String {
    return key.string
}

private final class BundleToken {}

Note: file format is similar to what SwiftGen generates.

You can later reference such a constant in code like this:

self.yourTextField.text = tr(.MenuItemAbout)