google-spreadsheet-dts

Check out the Online Sandbox!

This library automatically generates TypeScript types (*.d.ts) by parsing Google Sheets. It is useful when using Google Sheets to manage event tracking systems (such as Mixpanel and Google Analytics) or internationalization (I18N) data.

💫 Features

Parser presets and Generate types(*.d.ts) for public and private Google Sheets
Customizable type and file name
Import types from other files

Performance

MacBook Air (M1, 2020, 16GB)
Node.js v20.10.0

$ pnpm bench

name                               hz      min     max     mean      p75     p99    p995    p999     rme  samples
createDtsBody :: 100000 Rows  10.6335  76.2435  116.52  94.0420  98.9237  116.52  116.52  116.52  ±9.26%       10   fastest

performance.bench.ts

📦 Install

npm i --save-dev google-spreadsheet-dts
yarn add -D google-spreadsheet-dts
pnpm add -D google-spreadsheet-dts

🚀 Usage

1. Select a Google Sheets parser preset

google-spreadsheet-dts provides parser presets for public and private Google Sheets. Check out the parser presets here. You can also create custom parsers if needed.

2. Generate types with Google Sheets parser

Create the file scripts/generate-google-sheets-dts.ts. Here we use the parser preset publicGoogleSheetsParser. You can check the sheet content here.

// scripts/generate-google-sheets-dts.ts

import { generateDtsFile } from 'google-spreadsheet-dts';
import { resolve } from 'node:path';
import { publicGoogleSheetsParser } from 'google-spreadsheet-dts/parser';

generateDtsFile({
  name: 'GoogleSheets',
  directory: resolve(__dirname, '../src'),

  createDtsBody: {
    // Define the types to import in the dts file
    importTypes: [
      {
        name: 'StatusEnum',
        from: './StatusEnum',
      }
    ]
  }

  parser: publicGoogleSheetsParser(
    {
      spreadsheetId: '1j23zhzHcPd_LzDQ7uPrXgMJfPoZYs289boUKoKnAjUo',
    },
    {
      path: ['Key', 'Property'],
      typeName: 'Type',
    },
  ),
});

Now, the types can be generated with the following command. The types are generated in the file src/GoogleSheets.ts.

ts-node scripts/generate-google-sheets-dts.ts

Using JavaScript

// scripts/generate-google-sheets-dts.js

const { generateDtsFile } = require('google-spreadsheet-dts');
const { resolve } = require('node:path');
const { publicGoogleSheetsParser } = require('google-spreadsheet-dts/parser');

generateDtsFile({
  name: 'GoogleSheets',
  directory: resolve(__dirname, '../src'),

  createDtsBody: {
    importTypes: [
      {
        name: 'StatusEnum',
        from: './StatusEnum',
      }
    ]
  }

  parser: publicGoogleSheetsParser(
    {
      spreadsheetId: '1j23zhzHcPd_LzDQ7uPrXgMJfPoZYs289boUKoKnAjUo',
    },
    {
      path: ['Key', 'Property'],
      typeName: 'Type',
    },
  ),
});

node scripts/generate-google-sheets-dts.js

3. Use generated types

The above command generates the src/GoogleSheets.d.ts file as follows:

// src/GoogleSheets.d.ts

// Generated by google-spreadsheet-dts
/* eslint-disable */
/* prettier-ignore */
// @ts-nocheck
// noinspection JSUnusedGlobalSymbols
import { StatusEnum } from './StatusEnum';
export {};
declare global {
  export interface GoogleSheets {
    click_conversation_data: {
      conversation_id: string;
      created_at: Date;
      agent_type: string;
      status: StatusEnum;
      generate_position: 'conversation' | 'playground';
    };
    click_message_feedback_button: {
      conversation_id: string;
      message_id: string;
      generated_position: 'conversation' | 'playground';
      my_test: string | 'string';
    };
  }
}

The generated types are now available for use. Since the types are declared globally, you can use them without importing.

// src/my-event.ts

export const event = <T extends GoogleSheets, K extends keyof T>({
  action,
  properties,
}: {
  action: K;
  properties: Partial<T[K]>;
}) => {
  // ...
};

// ✅ OK
event({
  action: 'click_conversation_data',
  properties: {
    generate_position: 'conversation',
  },
});

// ❌ Compile Error
event({
  action: 'click_conversation_data',
  properties: {
    generate_position: 'invalid', // TSError: Type '"invalid"' is not assignable to type '"conversation" | "playground"'.
  },
});

📚 API

`generateDtsFile`

function generateDtsFile(params: GenerateDtsFileParams): Promise<void>;

type Params = {
  name: string;
  directory: string;
  parser: Parser;
  options?: GenerateDtsFileOptions;
};

type Parser = () => Promise<object> | object;

type GenerateDtsFileOptions = Partial<{
  fileName: string;
  output: (dts: string) => unknown;
  createDtsBody: CreateDtsOptions;
}>;

name: Name of the type. If options.fileName is not specified, it will also be used as the file name.
directory: Directory where the generated type file will be located.
parser: A function that defines how to parse the Google Sheets. You can use parser presets.
options
- fileName: Name of the type file to generate. The default is the name parameter.
- output: A function that defines how to use the generated types. By default, it saves to a file.
- createDtsBody: Options to customize the contents of the generated type file. See the createDtsBody section for details.

To create a custom parser, see the Writing a custom parser section.

`createDtsBody`

function createDtsBody(
  name: string,
  object: object,
  options?: CreateDtsOptions,
): string;

type CreateDtsOptions = Partial<{
  defaultType: string;
  importTypes: Import[];
}>;

type Import = {
  name: string;
  from: string;
};

name: Name of the type.
object: Content for the types.
options
- defaultType: Type to use when the sheet type is an empty string, undefined, or null. Default is any.
- importTypes: Types to import from inside the file.
  - name: Name of the type to import.
  - from: File path to import the type from.

👉 So, why should I use google-spreadsheet-dts?

Type Safety: You can statically validate the data at compile time.
Maintainability: You can anticipate the impact of changes to the sheet data in advance.
Productivity: You can write code that uses data more easily.

Without google-spreadsheet-dts

export const event = ({
  action,
  properties,
}: {
  action: string;
  properties: Record<string, unknown>;
}) => {
  // ...
};

// ✅ OK
event({
  action: 'click_conversation_data',
  properties: {
    generate_position: 'conversation',
  },
});

// ⚠️ Compile OK but Unexpected behavior
event({
  action: 'click_conversation_data',
  properties: {
    // 'invalid' is not a valid value for 'generate_position'
    generate_position: 'invalid',
  },
});

With google-spreadsheet-dts

export const event = <T extends GoogleSheets, K extends keyof T>({
  action,
  properties,
}: {
  action: K;
  properties: Partial<T[K]>;
}) => {
  // ...
};

// ✅ OK
event({
  action: 'click_conversation_data',
  properties: {
    generate_position: 'conversation',
  },
});

// ❌ Compile Error
event({
  action: 'click_conversation_data',
  properties: {
    generate_position: 'invalid', // TSError: Type '"invalid"' is not assignable to type '"conversation" | "playground"'
  },
});

✋ Limitations

Not Production Ready: This library is still in the early stages of development. Please use it with caution.

License

MIT @Gumball12