Update Notion Databases from a Google Sheet (with Apps Script)

Max Brawer
4 min readSep 14, 2022

--

Update: if you love Apps Script, you will love my new page www.sheetswizard.com/ where I am launching more products and content made to help you become the best operator in your entire organization!

Phew — after two days of trying to find a solution for how to accomplish a popular, reasonable set of tasks in Notion’s API, I wanted to do my duties as a Google Developer Expert and blog the solution for others to use.

Notion databases are like Google Sheets from another dimension. On the upside, they are very pretty and loaded with the project-management features that folks have shoehorned into Sheets for years like status dropdowns. On the downside, they are very static and not as on the grid for the dynamic data sources they hold. A project tracker that is hand-selected is a good use case. But a live data tracker is not. We need to use the great unifier, Apps Script, to help.

To quote my friend Garrett,

Notion should be clean, [Google] Docs/Sheets should be messy

So let’s clean up our Notion databases by learning how to update a Notion page from a Google Sheet and Google Apps Script:

Using the Notion API with Google Apps Script

First, let me give you what you came for — something to copy paste:

function updatePage() {let token = '[you need a token from Notion's integrations feature]';
let pageId = '[the underlying ID of the page, taken from the URL or the API itself]';
let props = {
"properties": {
"[The Property Name]": {
//in the case of a text field:
"rich_text": [{
"text": {
"content": "[The New Value!]"
}}]}}};
let options = {
"method": 'patch',
headers: {
"Content-Type": "application/json",
"Notion-Version": "2022-02-22",
"Authorization": "Bearer " + token
},
"payload": JSON.stringify(props),
"redirect": "follow"
};
let url = 'https://api.notion.com/v1/pages/'+pageId;
let response = UrlFetchApp.fetch(url,options);
Logger.log(JSON.parse(response.getContentText()));
}

It took a while to get through some very frustrating subtleties above, namely that “payload” is mandatory as a label rather than “body”, which works in API tools like Postman.

So, what did I just do? Let’s first see the use case. I have a database, a prettier version (don’t tell anyone I said that) of a Sheet:

I have a database, a prettier version (don’t tell anyone I said that) of a Sheet

I want to edit the “Role” field here, but the Notion API has so many methods. Turns out it is a “page property” we need to change:

I want to edit the “Role” field here, but the Notion API has so many methods. Turns out it is a “page property” we need to change.

In practice, I probably want to use apps script to do this 1000 times. But let’s do one for now. I want to change my Role to “Sheets Wizard”. I need to give the API a “patch” request that resembles the current value / structure, like:

let props = {
"properties": {
"[The Property Name]": {
//in the case of a text field:
"rich_text": [{
"text": {
"content": "[The New Value!]"
}}]}}};

How do I know this is the properties structure I need? First, I can simply get the page (read it) over the API to see the current structure. If I call:

function getNotionPage() {
let token = '...';
let pageid = 'pageid';
let url = 'https://api.notion.com/v1/pages/'+pageid;
let response = UrlFetchApp.fetch(url, { headers: {
'Notion-Version': '2022-02-22',
'Authorization': 'Bearer ' + token
}});
Logger.log(response);
}

…by following https://develop ers.notion.com/reference/retrieve-a-page, I can log the current structure of the page. This will tell me how it thinks of each row.

Finally, I simply change the values from current to desired. So this

let props = {
"properties": {
"Role": {
//in the case of a text field:
"rich_text": [{
"text": {
"content": "Newbie API User"
}}]}}};

Becomes:

let props = {
"properties": {
"Role": {
//in the case of a text field:
"rich_text": [{
"text": {
"content": "Sheets Wizard"
}}]}}};

Then you put it all together and bam — we have updated a database page (aka a Sheet Row in disguise) over the API.

Max is a Google Developer Expert and a leader in People Tech, Analytics & Operations. Opinions are the author’s own and not those of any employer. PS: I am gearing up to launch a Sheets education site, show your interest here: https://forms.gle/1KSA9Hf8qjUeXHzf6

--

--

Max Brawer

People Tech & Analytics leader @ Atlassian, formerly Twitch, BuzzFeed, Google, Nielsen | Try his apps @ sheetswizard.com