npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@amagaki/amagaki-plugin-google-sheets

v3.2.0

Published

[![NPM Version][npm-image]][npm-url] [![GitHub Actions][github-image]][github-url] [![TypeScript Style Guide][gts-image]][gts-url]

Downloads

5,176

Readme

amagaki-plugin-google-sheets

NPM Version GitHub Actions TypeScript Style Guide

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. Authenticate. See authentication for details.

  2. Add to amagaki.ts.

import {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);

    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

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                  |                    |
| qaz | capitalize   | a                   | b                  | c                  |

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
qaz: !IfLocale
  en: A
  de: B
  es: C

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

NOTE: This example includes a sample capitalize custom cell type. See below for details on using custom cell types.

Types

In the strings transformation, the type cell supports serialization as follows:

  • string: !pod.string objects
  • preferString: !pod.string objects, with the value used as the preferred default string
  • explicit: !IfLocale localized data, where blank values do not fall back
  • (blank): !IfLocale localized data, where blank values fall back to the default locale

You can define a custom cell type – your own serialization function to transform the data within a cell:

  1. Choose a name for the function.
  2. Use this name for the value of the type cell.
  3. Use the addCellType method on the plugin to register your type.
const sheets = GoogleSheetsPlugin.register(pod);
sheets.addCellType('capitalize', (data: string) => {
  return data.toUpperCase();
});

Custom cell types can also be used with the objectRows and grid transformations by specifying which header cells correspond to which cell types:

  1. Use addCellType to register the cell type with a name.
  2. When calling saveFile or bindCollection, supply a mapping of header cells to named cell types. See example below.
sheets.saveFile({
  podPath: '/content/partials/about.yaml',
  spreadsheetId: '1qP7IPYJ1nIA5useXKbm8nHyj96Ue_6YMEFkwgpUoL-c',
  range: 'about',
  transform: 'grid',
  columnsToCellTypes: {
    // Use the `capitalize` cell type for all cells with header `header1`.
    header1: 'capitalize',
  }
}),

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

Authentication

There are two ways to authenticate. We recommend using the application default identity (option 1), but using a service account key file is acceptable as well.

Option 1: Using application default credentials

  1. Install the gcloud SDK. See instructions.*
  2. Login and set the application default credentials. Ensure you provide the required scopes.
gcloud auth application-default login \
  --scopes=openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets
  1. That's it! Now, Amagaki will use the signed in Google Account to fetch content.

*NOTE: If you've never authenticated using gcloud before, after installing the SDK, you may need to set a default Google Cloud project. Use the command below after installing the gcloud SDK:

# Replace $PROJECT with your GCP project ID.
gcloud auth login
gcloud config set project $PROJECT
gcloud auth application-default set-quota-project $PROJECT

Option 2: Using a service account key file

  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 key.json is added to your .gitignore.
  2. Ensure the sheet is shared with the service account.
  3. Pass keyFile to the sheets plugin.
GoogleSheetsPlugin.register(pod, {
  keyFile: 'key.json',
});