@amagaki/amagaki-plugin-google-sheets

amagaki-plugin-google-sheets

An Amagaki plugin for fetching content from Google Sheets. Capable of transforming Google Sheets into various formats, and supports using Google Sheets for managing translations.

Use this to allow non-developers to manage content (including website copy, data, and translations) in a Google Sheet, and consuming that content within your Amagaki project to build a website.

Usage

1. Install the plugin.

npm install --save @amagaki/amagaki-plugin-google-sheets

1. Acquire a service account key file. You can do this interactively, from the IAM section of the Google Cloud Console, or you can do this via the gcloud CLI (see below for an example).

PROJECT=<Google Cloud Project ID>

# Create a service account named `amagaki`.
gcloud --project=$PROJECT \
  iam service-accounts create \
  amagaki

# Create a JSON key and download it to `key.json`.
gcloud --project=$PROJECT \
  iam service-accounts keys create \
  --iam-account amagaki@$PROJECT.iam.gserviceaccount.com \
  key.json

1. Ensure the sheet is shared with the service account.

2. Ensure key.json is added to your .gitignore.

3. Add to amagaki.ts.

import * as googleSheetsPlugin from '@amagaki/amagaki-plugin-google-sheets';
import {Pod, ServerPlugin} from '@amagaki/amagaki';

export default (pod: Pod) => {
  // Run Google Sheets plugin when dev server starts.
  const serverPlugin = pod.plugins.get('ServerPlugin') as ServerPlugin;
  serverPlugin.register(async () => {
    const sheets = googleSheetsPlugin.register(pod, {
      keyFile: 'key.json',
    });

    await Promise.all([
      // Binds a collection to specified tabs within the Google Sheet. Deletes
      // files from the collection that no longer exist in the sheet.
      // Because the `transform` value is set to `strings`, the plugin will also
      // import any translations contained within the sheets to their respective
      // locale files.
      sheets.bindCollection({
        collectionPath: '/content/strings',
        spreadsheetId: '1qP7IPYJ1nIA5useXKbm8nHyj96Ue_6YMEFkwgpUoL-c',
        ranges: ['homepage', 'about'],
        transform: 'strings',
      }),

      // Saves a single file, "homepage" tab, `strings` transformation.
      sheets.saveFile({
        podPath: '/content/partials/homepage.yaml',
        spreadsheetId: '1qP7IPYJ1nIA5useXKbm8nHyj96Ue_6YMEFkwgpUoL-c',
        range: 'homepage',
        transform: 'strings',
      }),

      // Save a single file, "about" tab, `grid` transformation.
      sheets.saveFile({
        podPath: '/content/partials/about.yaml',
        spreadsheetId: '1qP7IPYJ1nIA5useXKbm8nHyj96Ue_6YMEFkwgpUoL-c',
        range: 'about',
        transform: 'grid',
      }),

      // Save a single file, "about" tab, `objectRows` transformation.
      sheets.saveFile({
        podPath: '/content/partials/aboutObjectRows.yaml',
        spreadsheetId: '1qP7IPYJ1nIA5useXKbm8nHyj96Ue_6YMEFkwgpUoL-c',
        range: 'about',
        transform: 'objectRows',
      }),
    ]);
  });
};

Transform options

• strings
• grid
• objectRows
• rows (default)

strings

Use the strings format when managing website copy (and optionally translation strings) inside a Google Sheet. Non-translation data can also be added, by leaving the type field blank. For data that shouldn't fallback to the default en locale, use explicit in the type field.

Converts a sheet formatted as a grid of strings into a mapping of keys to localized strings. Additional non-string types can be added to manage localized data. The sheet must be in the following format:

| key  | type         | en        | de      | es    |
| ---- | ------------ | --------- | ------- | ----- |
| foo  | string       | Hello     | Hallo   | Hola  |
| bar  | string       | Bye       | Tschüss | Adiós |
| bar  | preferString | Goodbye   |         |       |
| baz  |              | https://example.com | https://example.de | https://example.es |
| qux  | explicit     | a         | b       |       |

The values are transformed to:

foo: !pod.string Hello
bar: !pod.string
  prefer: Goodbye
  value: Bye
baz: !IfLocale
  default: https://example.com
  de: https://example.de
  es: https://example.es
qux: !IfLocale
  en: a
  de: b

Furthermore, any translation strings denoted by type "string" within the sheet are automatically saved to the pod's locale files. Any rows that do not have type "string" are not imported to the locale files.

To refer to your Google Sheets data from documents, use the !pod.yaml YAML type. The content can be accessed in a template regularly. !pod.string types are automatically translated, and !IfLocale types are automatically localized.

partials:
- partial: hero
  headline: !pod.yaml /content/strings/homepage.yaml?foo
  body: !pod.yaml /content/strings/homepage.yaml?bar
  button:
    url: !pod.yaml /content/strings/homepage.yaml?baz

grid

Converts a sheet formatted as a grid of strings into a mapping of keys to headers to values. The sheet must be in the following format:

| <BLANK>  | header1 | header2 |
| -------- | ------- | ------- |
| foo      | a       | b       |
| bar      | c       | d       |

The values are transformed to:

foo:
  header1: a
  header2: b
bar:
  header1: c
  header2: d

objectRows

Converts a sheet formatted as a grid of strings into a list of objects mapping headers to values. The sheet must be in the following format:

| header1  | header2 | header3 |
| -------- | ------- | ------- |
| foo      | a       | b       |
| bar      | c       | d       |

The values are transformed to:

- header1: foo
  header2: a
  header3: b
- header1: bar
  header2: c
  header3: d

rows (default)

Does not modify response from Google Sheets. The sheet is simply serialized as a list of lists.

| header1  | header2 | header3 |
| -------- | ------- | ------- |
| foo      | a       | b       |
| bar      | c       | d       |

The values are transformed to:

- - header1
  - header2
  - header3
- - foo
  - a
  - b
- - bar
  - c
  - d