Introduction
This blog is very related to my previous blog related to the use of Google Spreadsheet as "CMS". You can read it first to understand the previous context first before going further.
The idea came from my current hobby by the way. I usually go to some cosplay or Japanese Cultural events around the Jabodetabek area. I got the general event list that provided by the cosplay communities. But because it's only a Google Spreadsheet, I think it's not user-friendly and readable for some people. That's why I think it's better to create a public AI that gets the data from it, so everyone can create the web based on it too.
Here's some highlight process that I've been going through to create the Japanese Culture Event List API.
Creating the endpoint using Express JS
I used Express JS in this mini-project. I create the base project structure by using these commands.
mkdir [project-name] pnpm init pnpm install dontenv express googleapis pnpm install -D @types/express @types/node nodemon ts-node typescriptbash
dotenv is for loading the env vars. express is the JS backend library that I use for this project. And googleapis is the official Google client library for Node.js. The dev deps that I use are Typescript related because I'm using Typescript here.
Here's my package.json content.
{ "name": "list-event-jjp-api", "version": "1.0.0", "description": "", "main": "dist/index.js", "scripts": { "dev": "nodemon api/index.ts" }, "keywords": [], "author": "", "license": "ISC", "dependencies": { "dotenv": "^16.4.5", "express": "^4.19.2", "googleapis": "^137.1.0" }, "devDependencies": { "@types/express": "^4.17.21", "@types/node": "^20.12.12", "nodemon": "^3.1.1", "ts-node": "^10.9.2", "typescript": "^5.4.5" } }json
Don't forget to setup the tsconfig.json also if using Typescript.
{ "compilerOptions": { /* Visit https://aka.ms/tsconfig to read more about this file */ /* Projects */ // "incremental": true, /* Save .tsbuildinfo files to allow for incremental compilation of projects. */ // "composite": true, /* Enable constraints that allow a TypeScript project to be used with project references. */ // "tsBuildInfoFile": "./.tsbuildinfo", /* Specify the path to .tsbuildinfo incremental compilation file. */ // "disableSourceOfProjectReferenceRedirect": true, /* Disable preferring source files instead of declaration files when referencing composite projects. */ // "disableSolutionSearching": true, /* Opt a project out of multi-project reference checking when editing. */ // "disableReferencedProjectLoad": true, /* Reduce the number of projects loaded automatically by TypeScript. */ /* Language and Environment */ "target": "es2016" /* Set the JavaScript language version for emitted JavaScript and include compatible library declarations. */, // "lib": [], /* Specify a set of bundled library declaration files that describe the target runtime environment. */ // "jsx": "preserve", /* Specify what JSX code is generated. */ // "experimentalDecorators": true, /* Enable experimental support for legacy experimental decorators. */ // "emitDecoratorMetadata": true, /* Emit design-type metadata for decorated declarations in source files. */ // "jsxFactory": "", /* Specify the JSX factory function used when targeting React JSX emit, e.g. 'React.createElement' or 'h'. */ // "jsxFragmentFactory": "", /* Specify the JSX Fragment reference used for fragments when targeting React JSX emit e.g. 'React.Fragment' or 'Fragment'. */ // "jsxImportSource": "", /* Specify module specifier used to import the JSX factory functions when using 'jsx: react-jsx*'. */ // "reactNamespace": "", /* Specify the object invoked for 'createElement'. This only applies when targeting 'react' JSX emit. */ // "noLib": true, /* Disable including any library files, including the default lib.d.ts. */ // "useDefineForClassFields": true, /* Emit ECMAScript-standard-compliant class fields. */ // "moduleDetection": "auto", /* Control what method is used to detect module-format JS files. */ /* Modules */ "module": "commonjs" /* Specify what module code is generated. */, // "rootDir": "./", /* Specify the root folder within your source files. */ // "moduleResolution": "node10", /* Specify how TypeScript looks up a file from a given module specifier. */ // "baseUrl": "./", /* Specify the base directory to resolve non-relative module names. */ // "paths": {}, /* Specify a set of entries that re-map imports to additional lookup locations. */ // "rootDirs": [], /* Allow multiple folders to be treated as one when resolving modules. */ // "typeRoots": [], /* Specify multiple folders that act like './node_modules/@types'. */ // "types": [], /* Specify type package names to be included without being referenced in a source file. */ // "allowUmdGlobalAccess": true, /* Allow accessing UMD globals from modules. */ // "moduleSuffixes": [], /* List of file name suffixes to search when resolving a module. */ // "allowImportingTsExtensions": true, /* Allow imports to include TypeScript file extensions. Requires '--moduleResolution bundler' and either '--noEmit' or '--emitDeclarationOnly' to be set. */ // "resolvePackageJsonExports": true, /* Use the package.json 'exports' field when resolving package imports. */ // "resolvePackageJsonImports": true, /* Use the package.json 'imports' field when resolving imports. */ // "customConditions": [], /* Conditions to set in addition to the resolver-specific defaults when resolving imports. */ // "resolveJsonModule": true, /* Enable importing .json files. */ // "allowArbitraryExtensions": true, /* Enable importing files with any extension, provided a declaration file is present. */ // "noResolve": true, /* Disallow 'import's, 'require's or '<reference>'s from expanding the number of files TypeScript should add to a project. */ /* JavaScript Support */ // "allowJs": true, /* Allow JavaScript files to be a part of your program. Use the 'checkJS' option to get errors from these files. */ // "checkJs": true, /* Enable error reporting in type-checked JavaScript files. */ // "maxNodeModuleJsDepth": 1, /* Specify the maximum folder depth used for checking JavaScript files from 'node_modules'. Only applicable with 'allowJs'. */ /* Emit */ // "declaration": true, /* Generate .d.ts files from TypeScript and JavaScript files in your project. */ // "declarationMap": true, /* Create sourcemaps for d.ts files. */ // "emitDeclarationOnly": true, /* Only output d.ts files and not JavaScript files. */ // "sourceMap": true, /* Create source map files for emitted JavaScript files. */ // "inlineSourceMap": true, /* Include sourcemap files inside the emitted JavaScript. */ // "outFile": "./", /* Specify a file that bundles all outputs into one JavaScript file. If 'declaration' is true, also designates a file that bundles all .d.ts output. */ "outDir": "./dist" /* Specify an output folder for all emitted files. */, // "removeComments": true, /* Disable emitting comments. */ // "noEmit": true, /* Disable emitting files from a compilation. */ // "importHelpers": true, /* Allow importing helper functions from tslib once per project, instead of including them per-file. */ // "importsNotUsedAsValues": "remove", /* Specify emit/checking behavior for imports that are only used for types. */ // "downlevelIteration": true, /* Emit more compliant, but verbose and less performant JavaScript for iteration. */ // "sourceRoot": "", /* Specify the root path for debuggers to find the reference source code. */ // "mapRoot": "", /* Specify the location where debugger should locate map files instead of generated locations. */ // "inlineSources": true, /* Include source code in the sourcemaps inside the emitted JavaScript. */ // "emitBOM": true, /* Emit a UTF-8 Byte Order Mark (BOM) in the beginning of output files. */ // "newLine": "crlf", /* Set the newline character for emitting files. */ // "stripInternal": true, /* Disable emitting declarations that have '@internal' in their JSDoc comments. */ // "noEmitHelpers": true, /* Disable generating custom helper functions like '__extends' in compiled output. */ // "noEmitOnError": true, /* Disable emitting files if any type checking errors are reported. */ // "preserveConstEnums": true, /* Disable erasing 'const enum' declarations in generated code. */ // "declarationDir": "./", /* Specify the output directory for generated declaration files. */ // "preserveValueImports": true, /* Preserve unused imported values in the JavaScript output that would otherwise be removed. */ /* Interop Constraints */ // "isolatedModules": true, /* Ensure that each file can be safely transpiled without relying on other imports. */ // "verbatimModuleSyntax": true, /* Do not transform or elide any imports or exports not marked as type-only, ensuring they are written in the output file's format based on the 'module' setting. */ // "allowSyntheticDefaultImports": true, /* Allow 'import x from y' when a module doesn't have a default export. */ "esModuleInterop": true /* Emit additional JavaScript to ease support for importing CommonJS modules. This enables 'allowSyntheticDefaultImports' for type compatibility. */, // "preserveSymlinks": true, /* Disable resolving symlinks to their realpath. This correlates to the same flag in node. */ "forceConsistentCasingInFileNames": true /* Ensure that casing is correct in imports. */, /* Type Checking */ "strict": true /* Enable all strict type-checking options. */, // "noImplicitAny": true, /* Enable error reporting for expressions and declarations with an implied 'any' type. */ // "strictNullChecks": true, /* When type checking, take into account 'null' and 'undefined'. */ // "strictFunctionTypes": true, /* When assigning functions, check to ensure parameters and the return values are subtype-compatible. */ // "strictBindCallApply": true, /* Check that the arguments for 'bind', 'call', and 'apply' methods match the original function. */ // "strictPropertyInitialization": true, /* Check for class properties that are declared but not set in the constructor. */ // "noImplicitThis": true, /* Enable error reporting when 'this' is given the type 'any'. */ // "useUnknownInCatchVariables": true, /* Default catch clause variables as 'unknown' instead of 'any'. */ // "alwaysStrict": true, /* Ensure 'use strict' is always emitted. */ // "noUnusedLocals": true, /* Enable error reporting when local variables aren't read. */ // "noUnusedParameters": true, /* Raise an error when a function parameter isn't read. */ // "exactOptionalPropertyTypes": true, /* Interpret optional property types as written, rather than adding 'undefined'. */ // "noImplicitReturns": true, /* Enable error reporting for codepaths that do not explicitly return in a function. */ // "noFallthroughCasesInSwitch": true, /* Enable error reporting for fallthrough cases in switch statements. */ // "noUncheckedIndexedAccess": true, /* Add 'undefined' to a type when accessed using an index. */ // "noImplicitOverride": true, /* Ensure overriding members in derived classes are marked with an override modifier. */ // "noPropertyAccessFromIndexSignature": true, /* Enforces using indexed accessors for keys declared using an indexed type. */ // "allowUnusedLabels": true, /* Disable error reporting for unused labels. */ // "allowUnreachableCode": true, /* Disable error reporting for unreachable code. */ /* Completeness */ // "skipDefaultLibCheck": true, /* Skip type checking .d.ts files that are included with TypeScript. */ "skipLibCheck": true /* Skip type checking all .d.ts files. */ } }json
Then, I created the folder /api and index.ts in there.
Like common APIs that are made using Express JS, here's my code declaration. I'll use the sheet variable that using the Google Sheet API to collect the data.
import express, { Express, Request, Response } from "express"; import { google } from "googleapis"; import dotenv from "dotenv"; const app: Express = express(); const port = 3000; dotenv.config(); const sheet = google.sheets({ version: "v4", auth: process.env.KEY });ts
I create two endpoints here. The first endpoint is / which get all the event list from the Google Sheet. It also can be filtered by the city name.
app.get("/", async (req: Request, res: Response) => { // recieve query param named city const city = req.query.city as string; const eventList = await sheet.spreadsheets.values .get({ spreadsheetId: process.env.SPREADSHEET_ID, range: "Event", }) .then((response: any) => { // remove the first two index because it's only label, not the data return response.data.values?.slice(2).map((col: any[]) => { return { event_date: col[0], event_name: col[4], event_city: col[3], event_location: col[2], event_info_link: col[6], }; }); }); res .status(200) .send( city ? eventList.filter((event: any) => event.event_city.toLowerCase().includes(city.toLocaleLowerCase()) ) : eventList ); });ts
The second endpoint is /cities which return the list of cities. I use JS Set to make it unique for each cities.
app.get("/cities", async (req: Request, res: Response) => { // return unique cities const eventList = await sheet.spreadsheets.values .get({ spreadsheetId: process.env.SPREADSHEET_ID, range: "Event", }) .then((response: any) => { return response.data.values?.slice(2).map((col: any[]) => { return col[3]; }); }); const uniqueCities = [...new Set(eventList)]; res.status(200).send(uniqueCities); });ts
In the end, here's my snippet to make sure the server run well in my local device and do the module export.
app.listen(port, () => { console.log(`Server is running now!`); }); module.exports = app;ts
To run and test it in the local device, just run pnpm dev. It will run in localhost:3000. It will return the base list data.
[ { "event_date": "28 Mei 2024", "event_name": "J-vent 2024 Culinary and culture", "event_city": "Jakarta", "event_location": "Teater Terbuka, Universitas Negeri Jakarta", "event_info_link": "https://www.facebook.com/groups/251875943835/permalink/10162493579508836/" }, { "event_date": "30 Mei 2024", "event_name": "Showroom Event Cosplay Competition", "event_city": "Kalimantan Timur", "event_location": "Dealer Astra Motor, Tenggarong, KalTim", "event_info_link": "https://www.facebook.com/groups/251875943835/permalink/10162503179373836/" }, ... ]json
Want to see my full source code? You can access it here. For deployment purposes, I used Vercel. If you want to use Vercel too to deploy your API, you can follow the guide here.
Creating the API Docs using Postman
For the documentation purpose, I used Postman because I'm familiar with it😅. Here's a quick guide to creating API Documentation using Postman.
Make sure you already have the Postman Desktop App and create an account when you open it for the first time. On the tab Collections click the New button to create a new collection.
To simplify the process, you can select the template that has been provided to you. For me, I choose the REST API Basics template as a starter template.
After that, you can make changes to the project description based on your needs.
If you're ready to publish your own API Docs, you can click the Publish button on the top right menu. You can read the full steps in the Postman Official Documentation.
Closing
This is just a simple example of the Google Sheet API usage. As I've told you in the previous article, this seems trivial but actually, this kind of tool can be utilized for many things for our needs as a software engineer. You can use Google Sheet API as a "CMS" or even like in this article, you can create REST API through Google Sheet API.
I hope this article can help you when you have to deliver a web app with a tight deadline, you can use this as a reference.