---
title: roapiを使ってGoogle SheetsのスプレッドシートをPostgreSQLのクエリでアクセスするメモ
tags: ["PostgreSQL", "roapi", "Google Sheets"]
categories: ["Middleware", "RDBMS", "PostgreSQL"]
date: 2024-06-13T12:59:20Z
updated: 2024-06-13T14:17:46Z
---

https://github.com/roapi/roapi

### Google Sheets APIのService Account作成

https://console.developers.google.com/apis/library/sheets.googleapis.com


<img width="774" src="https://github.com/making/blog.ik.am/assets/106908/9bcae3dd-1dbc-4540-98e1-ec2ed2c7bacd">
<br>

<img width="635" src="https://github.com/making/blog.ik.am/assets/106908/3720333b-7b38-4c30-8e96-5d17b618c872">
<br>

<img width="963" src="https://github.com/making/blog.ik.am/assets/106908/12f5a3b8-7f48-4163-9fc8-ee013ac77256">
<br>

<img width="930" src="https://github.com/making/blog.ik.am/assets/106908/d5ee9ec1-73cd-4e6b-88c5-421c5ca656de">
<br>

<img width="975" src="https://github.com/making/blog.ik.am/assets/106908/ee7dcc35-b50b-41ce-bfce-b3106b6d7542">
<br>

<img width="1166" alt="image" src="https://github.com/making/blog.ik.am/assets/106908/4259161e-abf3-47a3-b91f-78c36f6e739d">
<br>

<img width="928" src="https://github.com/making/blog.ik.am/assets/106908/f6b9c232-ad90-4c83-acc4-9326e2790065">
<br>

<img width="613" src="https://github.com/making/blog.ik.am/assets/106908/a49b2b1f-0122-4cdd-b0d4-a8f50af52cdd">

### スプレッドシート作成

https://docs.google.com/spreadsheets/d/1-lc4oij04aXzFSRMwVBLjU76s-K0-s6UPc2biOvtuuU/edit#gid=0 をコピー

<img width="1241" alt="image" src="https://github.com/making/blog.ik.am/assets/106908/8a83dd12-0a6d-4eb6-a73f-e8738d066987">
<br>

<img width="622" src="https://github.com/making/blog.ik.am/assets/106908/55856b29-576c-4569-9fe5-0c50e7b21211">

### roapiの設定

```
brew install roapi
```

```yaml
addr:
  http: 0.0.0.0:8084
  postgres: 0.0.0.0:5433
tables:
- name: properties
  uri: https://docs.google.com/spreadsheets/d/1PdkN-8sx5VhpfV49kTvhFOjOOt2QiD3G-Wi8_zwv-gA/edit#gid=0
  option:
    format: google_spreadsheet
    application_secret_path: /path-to/service-account-key.json
```

```
./roapi -c roapi.yaml 
```


```
$ psql postgres://localhost:5433
psql (16.1, server 13)
Type "help" for help.

tmaki=> 
```

```
tmaki=> select * from properties;
     Address      |                                                     Image                                                      | Landlord | Bed | Bath | Occupied | Monthly_Rent | Lease_Expiration_Date | Days_Until_Expiration 
------------------+----------------------------------------------------------------------------------------------------------------+----------+-----+------+----------+--------------+-----------------------+-----------------------
 Bothell, WA      | https://live.staticflickr.com/2438/5717057107_c0a14150d9_b.jpg                                                 | Roger    |   3 |    2 | f        | $2,000       | 10/23/2020            | Expired
 Lynnwood, WA     | https://assets.perfectplaces.com/prop_image/22/225287_342_lg.jpg                                               | Daniel   |   2 |    1 | f        | $1,700       | 6/10/2019             | Expired
 Kirkland, WA     | https://live.staticflickr.com/5218/5417441127_7e7c23788a_b.jpg                                                 | Mike     |   4 |    2 | f        | $3,000       | 6/24/2021             | Expired
 Kent, WA         | http://www.nprents.com/uploads/8/4/1/6/8416147/005-side-view_orig.jpg                                          | Mike     |   3 |    2 | f        | $3,800       | 10/31/2020            | Expired
 Mount Vernon, WA | https://odis.homeaway.com/odis/listing/11435da1-001d-48cd-b691-4be5af59a66a.c6.jpg                             | Roger    |   2 |    1 | f        | $1,500       | 11/5/2019             | Expired
 Seattle, WA      | https://images.pexels.com/photos/1396132/pexels-photo-1396132.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500       | Carl     |   3 |    1 | f        | $3,000       | 12/28/2021            | Expired
 Seattle, WA      | https://images.pexels.com/photos/106399/pexels-photo-106399.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500         | Daniel   |   2 |    1 | f        | $1,500       | 4/29/2021             | Expired
 Shoreline, WA    | https://images.pexels.com/photos/1834739/pexels-photo-1834739.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500       | Roger    |   1 |    1 | f        | $1,200       | 12/9/2021             | Expired
 Bellevue, WA     | https://i.pinimg.com/736x/c8/9c/2d/c89c2d527262492311db975e4344be32--fireplace-on-porch-lodge-style.jpg        | Mike     |   3 |    1 | f        | $2,400       | 2/15/2020             | Expired
 Renton, WA       | https://images.pexels.com/photos/1569003/pexels-photo-1569003.jpeg?auto=compress&cs=tinysrgb&dpr=2&h=650&w=940 | Carl     |   4 |    2 | f        | $2,800       | 10/22/2021            | Expired
 Woodinville, WA  | https://images.pexels.com/photos/2102587/pexels-photo-2102587.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500       | Carl     |   3 |    3 | f        | $3,000       | 5/30/2019             | Expired
 Kenmore, WA      | https://images.pexels.com/photos/376547/pexels-photo-376547.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500         | Sam      |   4 |    3 | f        | $4,000       | 9/22/2019             | Expired
 Fremont, WA      | https://images.pexels.com/photos/259588/pexels-photo-259588.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500         | Daniel   |   5 |    3 | f        | $4,500       | 7/13/2019             | Expired
 Redmond, WA      |                                                                                                                | Mike     |   2 |    2 | f        | $2,200       | 5/31/2020             | Expired
 Mill Creek, WA   | https://images.pexels.com/photos/1974596/pexels-photo-1974596.jpeg?auto=compress&cs=tinysrgb&dpr=1&w=500       | Sam      |   3 |    3 | f        | $3,500       | 8/4/2021              | Expired
(15 rows)
```