Log inSign Up
Back to APIs list

Google Sheets API examples & templates

Use these vals as a playground to view and fork Google Sheets API examples and templates on Val Town. Run any example below or find templates that can be used as a pre-built solution.

gsheet_call

Wrapper around Google Sheets API v4.

Parameters

  • service_account: JSON string containing Google Service Account key
  • sheet_id: Google Sheet ID
  • method: HTTP method to use
  • action: Full URL with https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ removed
  • data: HTTP request body

Requirements

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheet_id parameter)

Instructions

  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, then set it as a secret. Use the secret for the service_account parameter.
  3. Figure out the action you want to perform. You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  4. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
export const gsheet_call = async (service_account, sheet_id, method, action, data) => {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const token = await getToken(service_account, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/${action}`,
{
method,
headers: {
Authorization: `Bearer ${token.access_token}`,
},
body: (method == "GET" || method == "HEAD") ? undefined : JSON.stringify(data),
},
);
return result;
};
0

gsheet_call

Wrapper around Google Sheets API v4.

Parameters

  • service_account: JSON string containing Google Service Account key
  • sheet_id: Google Sheet ID
  • method: HTTP method to use
  • action: Full URL with https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ removed
  • data: HTTP request body

Requirements

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheet_id parameter)

Instructions

  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, then set it as a secret. Use the secret for the service_account parameter.
  3. Figure out the action you want to perform. You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  4. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
export const gsheet_call = async (service_account, sheet_id, method, action, data) => {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const token = await getToken(service_account, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/${action}`,
{
method,
headers: {
Authorization: `Bearer ${token.access_token}`,
},
body: (method == "GET" || method == "HEAD") ? undefined : JSON.stringify(data),
},
);
return result;
};
1
0

gsheet_call

Wrapper around Google Sheets API v4.

Parameters

  • service_account: JSON string containing Google Service Account key
  • sheet_id: Google Sheet ID
  • method: HTTP method to use
  • action: Full URL with https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ removed
  • data: HTTP request body

Requirements

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheet_id parameter)

Instructions

  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, then set it as a secret. Use the secret for the service_account parameter.
  3. Figure out the action you want to perform. You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  4. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
export const gsheet_call = async (service_account, sheet_id, method, action, data) => {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const token = await getToken(service_account, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/${action}`,
{
method,
headers: {
Authorization: `Bearer ${token.access_token}`,
},
body: (method == "GET" || method == "HEAD") ? undefined : JSON.stringify(data),
},
);
return result;
};
2
0

gsheet_call

Wrapper around Google Sheets API v4.

Parameters

  • service_account: JSON string containing Google Service Account key
  • sheet_id: Google Sheet ID
  • method: HTTP method to use
  • action: Full URL with https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ removed
  • data: HTTP request body

Requirements

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheet_id parameter)

Instructions

  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, then set it as a secret. Use the secret for the service_account parameter.
  3. Figure out the action you want to perform. You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  4. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
export const gsheet_call = async (service_account, sheet_id, method, action, data) => {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const token = await getToken(service_account, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/${action}`,
{
method,
headers: {
Authorization: `Bearer ${token.access_token}`,
},
body: (method == "GET" || method == "HEAD") ? undefined : JSON.stringify(data),
},
);
return result;
};
1
0

gsheet_call

Wrapper around Google Sheets API v4.

Parameters

  • service_account: JSON string containing Google Service Account key
  • sheet_id: Google Sheet ID
  • method: HTTP method to use
  • action: Full URL with https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/ removed
  • data: HTTP request body

Requirements

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheet_id parameter)

Instructions

  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, then set it as a secret. Use the secret for the service_account parameter.
  3. Figure out the action you want to perform. You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  4. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
export const gsheet_call = async (service_account, sheet_id, method, action, data) => {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const token = await getToken(service_account, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/${action}`,
{
method,
headers: {
Authorization: `Bearer ${token.access_token}`,
},
body: (method == "GET" || method == "HEAD") ? undefined : JSON.stringify(data),
},
);
return result;
};
235
4
Fork
1
2
// set at Thu Nov 30 2023 14:22:53 GMT+0000 (Coordinated Universal Time)
export let topHNThreadByHour = ["Top thread on Hackernews for 3:00 is: Vespa.ai is spinning out of Yahoo as a separate company","Top thread on Hackernews for 4:00 is: President Speaking: Spoofing Alerts in 4G LTE Networks (2019) [pdf]","Top thread on Hacke
6
0

Wrapper around Google Sheets API v4. You will need:

  • a Google Cloud service account
  • the Google Sheets API v4 enabled in your Google Cloud project
  • the spreadsheet ID (provide it in the sheetId parameter)
  1. Share the spreadsheet with the service account
  2. Make a JSON key for the service account, minify it and set it as a secret.
  • Use the secret for the serviceAccount parameter.
  1. Figure out the action you want to perform.
  • You will need to provide everything that comes after {spreadsheetId}/ as the action parameter. For example: values/A1:C1:append?valueInputOption=RAW
  1. Figure out the request body. For example: {values: [["foo", "bar", "baz"]]}
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON?v=41";
type Args = {
serviceAccount: string;
sheetId: string;
action: string;
data: object;
};
export async function callGoogleSheetsAPI(
{ serviceAccount, sheetId, action, data }: Args,
) {
const { getToken } = await import(
"https://deno.land/x/google_jwt_sa@v0.2.3/mod.ts"
);
const googleAuthOptions = {
scope: ["https://www.googleapis.com/auth/spreadsheets"],
};
const { access_token } = await getToken(serviceAccount, googleAuthOptions);
const result = fetchJSON(
`https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/${action}`,
{
method: "POST",
bearer: access_token,
body: JSON.stringify(data),
},
);
return result;
}
3
2

Current Stock Price

This val was designed to be used in Google Sheets, particularly because Google Finance stopped supporting Spotify inexplicably.

Usage

  1. Copy and paste this into Google Sheets:
=IMPORTDATA(CONCATENATE("https://api.val.town/v1/run/stevekrouse.stockPrice?args=[%22","SPOT","%22]"))
  1. Replace "SPOT" with the symbol you want

API

This val currently uses Alpha Vantage's free plan (5 requests per minute & 500 per day).

You can sign up for your own API Key here: https://www.alphavantage.co/support/#api-key

Fork
1
2
3
4
5
6
7
8
9
10
import process from "node:process";
import { fetchJSON } from "https://esm.town/v/stevekrouse/fetchJSON";
export async function stockPrice(symbol: string) {
let data = await fetchJSON(
`https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=${symbol}&apikey=${process.env.alphaVantage}`,
);
console.log(data);
return data["Global Quote"]["05. price"];
}
31
2

Val Town inspiration & use cases list

List [as object] used in Val Town's use cases and inspiration. Check it out at in /examples/use-cases and /docs/tutorial/4

Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
// set by rodrigotello.updateInspoList at 2023-06-29T18:53:25.934Z
export let valTownInspoList = [{
"title": "What can I make in Val Town?",
"description":
"The data for this page was created by using Val Town as a CMS",
"val": "@rodrigotello.valTownInspoList",
image:
"https://air-prod.imgix.net/abff23bf-fc18-485f-a6b5-4a1c8b7f91ec.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "NASA photo of the day.",
"description": "Returns NASA's Astronomy Picture of the Day (APOD)",
"val": "@rodrigotello.nasaAPOD",
"image":
"https://air-prod.imgix.net/11ab8b2e-c052-4574-8341-96d5d74040cc.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "hnFollow",
"description":
"Get email notifications any time an author you follow posts in Hacker News.",
"val": "@rodrigotello.hnFollow",
"image":
"https://air-prod.imgix.net/61e08fed-c77e-4a7b-9ae5-1efabb349574.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "GitHub events",
"description": "Get a GithHub users' public events",
"val": "@stevekrouse.githubEvents",
image:
"https://air-prod.imgix.net/3545d2cb-7cae-49d9-98b7-832428d2af34.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Twitter alerts",
"description": "Track Twitter mentions and be alerted via email",
"val": "@stevekrouse.twitterAlert",
image:
"https://air-prod.imgix.net/4c81af31-0832-4c72-8afb-34ef23fa03c9.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Air quality",
"description":
"Get email alerts when the air quality is bad. Val created with much help from @russbiggs (Director of Technology at OpenAQ)",
"val": "@stevekrouse.aqi",
image:
"https://air-prod.imgix.net/1a21321c-2bb7-4aa8-8c26-eb58cf1787a2.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Annoy friends in Bluesky",
"description":
"ChatGPT powered bot for the sole purpose of annoying friends on Bluesky.",
"val": "@ajax.annoy",
image:
"https://air-prod.imgix.net/d21d3c1f-99e9-4356-85ed-bc6d7746c867.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Venue calendar",
"description": "Get a venue calendar through Resy",
"val": "@rlesser.Resy_getVenueCalendar",
image:
"https://air-prod.imgix.net/cab68838-bc60-48a2-8b00-50f203ac303d.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Bitcoin price alert",
"description": "Get an email when Bitcoin price changes more than 20%",
"val": "@stevekrouse.btcPriceAlert",
image:
"https://air-prod.imgix.net/0d6605d2-b3ab-4bcb-9cf4-06580c5007ee.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "RSS Notification",
"description":
"Your own RSS feed. Just create a list of the blogs you want to follow.",
"val": "@stevekrouse.pollRSSFeeds",
image:
"https://air-prod.imgix.net/9ad2b03e-ea41-4402-b18d-50df75f29d5e.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Uptime monitor",
"description": "Check if your website (or any website) is down.",
"val": "@healeycodes.isMyWebsiteDown",
image:
"https://air-prod.imgix.net/c662747c-92cd-4e3a-ac91-1209f7cf6fb5.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Stale PRs",
"description": "Get emails with the open PRs that have't had any activity.",
"val": "@ramkarthik.staleGithubPRsEmail",
image:
"https://air-prod.imgix.net/640fc428-31a1-44b3-a9ac-4af8474fa867.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Wholefoods RSS",
"description": "Track when your favorite products are back in stock.",
"val": "@Glench.wholeFoodsRSS",
image:
"https://air-prod.imgix.net/2ab8f2e0-3146-4c92-a4de-f0e49ef9e456.jpg?w=600&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Discord Welcome Bot",
"description":
'Create a Discord welcome bot using scheduled vals. You can <a href="https://docs.val.town/create-a-discord-welcome-bot">read the full tutorial here</a>.',
"val": "@vtdocs.discordWelcomeBotCron",
image:
"https://air-prod.imgix.net/9286aea4-98c3-4c6e-aa45-537932a51dfb.jpg?w=600&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Slack app",
"description":
"Evaluate anything from Slack via Val Town and get results back right in Slack.",
"val": "@nate.slackapp",
image:
"https://air-prod.imgix.net/70698db9-7715-4092-bbc1-be51ad08ff28.jpg?w=1200&h=2000&auto=compress&ixlib=react-9.5.4",
}, {
"title": "Spotify Discover Weekly Archive",
9
1
Fork
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import { exportedKeys } from "https://esm.town/v/stevekrouse/exportedKeys";
import { valTownInspoList } from "https://esm.town/v/rodrigotello/valTownInspoList?v=99";
import { runValAPIAuth } from "https://esm.town/v/stevekrouse/runValAPIAuth";
export let untitled_blackBoa = runValAPIAuth({
val: "@rodrigotello.updateInspoList",
args: [
[...valTownInspoList, {
title: "Current Stock Price",
description:
"Get the current price of a stock. This val is useful in Google Sheets as a Google Finance alternative.",
val: `@stevekrouse.stockPrice`,
}],
],
handle: "@stevekrouse",
keys: exportedKeys,
});
0
1
Next