AppSheetApiClient

A wrapper for Google AppSheet API that can be used in Node.JS as well as in advanced JavaScript frameworks like React, Vue.js, and Angular.

Enable API

1. Open the app in the app editor.
2. Select Settings > Integrations.
3. Under IN: from cloud services to your app, enable the Enable toggle.
4. This enables the API for the application as a whole.
5. Ensure that at least one unexpired Application Access Key is present.
6. Otherwise, click Create Application Access Key.

Referent document: AppSheet Help Center

Features

• Simplified AppSheet API usage
• Easy to build query string by Condition Expression and Query Expression

Installation

With npm:

npm install appsheet-client-api

With yarn:

yarn add appsheet-client-api

Getting started

First, you need create a AppSheet API Client instance.

const client = new AppSheetApiClient(appSheetAppId, appSheetKey, properties);

Add records to a table

add<T>(tableName: string, rows: T[]) => Promise<T[]>

Example:

const accounts: IAccount[] = [
  { email: '[email protected]', name: 'Foo' },
];

const createdRows = await client.add<IAccount>('Accounts', accounts);
//    ^? const createdRows: IAccount[]

Delete records from a table

delete<T>(tableName: string, rows: T[]) => Promise<T[]>

Example:

const accounts: IAccount[] = [
  { email: '[email protected]', name: 'Foo' },
];

const deletedRows = await client.delete<IAccount>('Accounts', accounts);
//    ^? const deletedRows: IAccount[]

Update records in a table

update<T>(tableName: string, rows: T[]) => Promise<T[]>

Example:

const accounts: IAccount[] = [
  { email: '[email protected]', name: 'Foo' },
];

const updatedRows = await client.update<IAccount>('Accounts', accounts);
//    ^? const updatedRows: IAccount[]

Read records from a table

Read existing records in a table using the API.

readAllRows<T>(tableName: string) => Promise<T[]>

This function will return all rows in the table.

Example:

const accounts = await client.readAllRows<IAccount>('Accounts');
//    ^? const accounts: IAccount[]

readByKeys<T>(tableName: string, keys: T[]) => Promise<T[]>

This will return all rows having matching key values from the table.

Example:

const accounts = await client.readByKeys<IAccount>('Accounts', [{ ID: '0001'}]);
//    ^? const accounts: IAccount[]

readSelectedRows<T>(tableName: string, selector: string | QueryExpression) => Promise<T[]>

In this function, you can specify an expression to select and format the rows returned.

Example: You want to filter data on Pepple table by following Selector Select(People[_ComputedKey], And([Age] >= 21, [State] = "CA"), true)

• Option 1: You can define selector as a string

const result = await client.readSelectedRows<IPeople>(
//    ^? const result: IPeople[]
  'People',
  'Select(People[_ComputedKey], And([Age] >= 21, [State] = "CA"), TRUE)',
);

• Option 2: Use Select Query Expression object

const selector: QueryExpression = {
  type: 'Select',
  fromDatasetColumn: 'People[_ComputedKey]',
  selectRow: 'And([Age] >= 21, [State] = "CA")',
  distinctOnly: 'TRUE',
};

const result = await client.readSelectedRows<IPeople>(
//    ^? const result: IPeople[]
  'People',
  selector,
);

• Option 3: Use Select Query Expression object and Condition Expression

const condition: ConditionExpression = {
  type: 'And',
  conditions: [
    '[Age] >= 21',
    '[State] = "CA"',
  ],
};

const selector: QueryExpression = {
  type: 'Select',
  fromDatasetColumn: 'People[_ComputedKey]',
  selectRow: condition,
  distinctOnly: 'TRUE',
};

const result = await client.readSelectedRows<IPeople>(
//    ^? const result: IPeople[]
  'People',
  selector,
);

Supported expression

Condition expressions

• AndExpression

export interface AndExpression {
  type: 'And';
  conditions: ConditionExpression[];
}

A comparison expression asserting that all conditions in the provided list are true.

Example:

const condition: ConditionExpression = {
  type: 'And',
  conditions: [
    '[Age] >= 21',
    '[Age] <= 30',
    '[State] = "CA"',
    '[City] = "Newport Beach"',
  ],
};
// => AND([Age] >= 21, [Age] <= 30, [State] = "CA", [City] = "Newport Beach")

• OrExpression

export interface OrExpression {
  type: 'Or';
  conditions: ConditionExpression[];
}

A comparison expression asserting that one or more conditions in the provided list are true.

Example:

const condition: ConditionExpression = {
  type: 'Or',
  conditions: [
    '[Age] >= 21',
    '[State] = "CA"',
  ],
};
// => OR([Age] >= 21, [State] = "CA")

• NotExpression

export interface NotExpression {
  type: 'Not';
  condition: ConditionExpression;
}

A comparison expression asserting that the provided condition is not true.

Example:

  const condition: ConditionExpression = {
  type: 'Not',
  condition: 'IN([City]), {"Newport Beach", "San Jose"}',
};
// => NOT(IN([City]), {"Newport Beach", "San Jose"})

You can combine one or more condition expression.

const condition: ConditionExpression = {
  type: 'And',
  conditions: [
    '[Age] >= 21',
    {
      type: 'Or',
      conditions: [
        {
          type: 'Not',
          condition: 'IN([City]), {"Newport Beach", "San Jose"}',
        },
        'ISBLANK([Fax])',
      ],
    },
  ],
};
// => AND([Age] >= 21, OR(NOT(IN([City]), {"Newport Beach", "San Jose"}), ISBLANK([Fax])))

Query expressions

export type FilterExpression = {
  type: 'Filter';
  dataset: string;
  selectRow: string | ConditionExpression;
}

export type OrderByExpression = {
  type: 'OrderBy';
  keys: string;
  orderBy: Array<[string, 'TRUE' | 'FALSE']>;
}

export type SelectExpression = {
  type: 'Select';
  fromDatasetColumn: string;
  selectRow: string | ConditionExpression;
  distinctOnly: 'TRUE' | 'FALSE';
}

These interfaces are mapping to AppSheet functions:

• FILTER() to return keys to rows in a table or slice.
• ORDERBY() expression to control the sort order of the returned records.
• SELECT() expression that yields a list of record key values. The records identified by the key values are returned in the Rows response. The SELECT() expression can refer to a slice.