theghostbel/gdocs-table-downloader

Use Google Spreadsheet as storage for your app translations. Or use this npm package to download your google spreadsheets as JSON files with just a single call.

📄➡📁 gdocs-table-downloader (GTD)

Download your translations from Google Spreadsheet. Very popular library to support a workflow when you store your translations in Google Spreadsheets. GTD downloads translations for you, considering your file structure.

In other words, having a spreadsheet like this:

You can get the result file like this:

Please, take a look at the example Google Spreadsheet file. GTD CI (Github Actions) use this file not only to ensure that the unit tests are passing but also to ensure that integration with Google Sheets API is in a shape.

📛 Our badges

🆘 How to use?

1. Install the package

   npm i gdocs-table-downloader -g
   

2. Set your Google Secrets to options.js (see customOptions.default.js for example)
3. Prepare your Google Spreadsheet:
   1. create a service account in Google Console
   2. add it to the list of granted users (it's enough to allow only read access)
4. Run it:

   gdocs-table-downloader --token XXX --sheets one,two --customOptions ./options.js --moduleType ESM --target ./out/{sheet}.{locale}.js
   

The token can be taken from the URL. The example spreadsheet https://docs.google.com/spreadsheets/d/1oFig-VwfFKP3BLsW4ZgLiw5ftAfcD4jpcUwmXBdhCPU/edit#gid=0 has token 1oFig-VwfFKP3BLsW4ZgLiw5ftAfcD4jpcUwmXBdhCPU

If your Google Spreadsheet had sheets one and two, each having, for example, en and de locales, you should get the next file structure:

folder-where-you-ran-this-script/
└─ out/
   ├─ one.de.js
   ├─ one.en.js
   ├─ two.de.js
   └─ two.en.js

⚙️ Options

See options.js, it's yargs config. Also, if you run this script and forgot to mention some required options, you would get a description of what you've missed.

📦 What is moduleType?

Currently, gdocs-table-downloader supports several types of modules in generated files: AMD, ESM, JSON. You can observe examples in test/expected.

AMD (Asynchronous Module Definition)

/* eslint quotes: 0 */
define({
  "row1": "cellValue"
})

ESM (ECMAScript module)

/* eslint quotes: 0 */
export default {
  "row1": "cellValue"
}

JSON

{
  "row1": "cellValue"
}

🙊 How to provide credentials to access Google Spreadsheets API?

Google Sheets API requires authentication since v4. gdocs-table-downloader is limited to the "service account" type. You should create such account in Google Console, and then you have several options:

1. Put private_key and client_email directly to a file specified by --customOptions param. See customOptions.default.js for example.
2. Put private_key and client_email to ENV and read them from process.env
3. Provide --auth ./path-to-auth.json to enable authentication. This file can be obtained from Google Console. See auth-example.json for example. Only private_key and client_email fields are required.

Frankly, you're not restricted to only these methods. You can invent any type of "secrets storing", just ensure that getGoogleAuthCredentials() returns an object with two properties: private_key and client_email.

P.S. Read an article on how to store multiline secrets in Circle CI

🛃 Custom value mappings

If you need to perform some custom mapping for cell values before they are downloaded, you can specify getValueMapper(rawCellValue) function in --customOptions file.

Every cell value goes through this function and the returned value is stored in a result file.

If you don't specify getValueMapper, the default function from customOptions.default.js would be used: it changes "undefined" values to empty strings.

How to release

1. Run locally version update:
   • SERVICE_ACCOUNT_PRIVATE_KEY=$(cat pem.txt) SERVICE_ACCOUNT_CLIENT_EMAIL='[email protected]' npm run update-patch
   • pem.txt is a private key frorm Google Console
2. Push new commit "Update to version x.x.x" together with tags: git push --follow-tags
3. Create release in GitHub
4. Wait actions to finish

License