구글 스프레드시트 데이터를 API로 가져오기

구글 스프레드시트의 데이터를 API로 가져오고, 웹페이지에서 이 데이터를 자유롭게 이용할 수 있는 방법이 없을까?

 

구글스프레드시트를 API로 변환해주는 Third party 서비스가 일부 있지만 (sheety, sheet2api, sheetdb)

대부분 유료여서 (무료 티어에는 제약이 많다) 토이프로젝트에서 자유롭게 데이터를 이용하기에는 어려움이 많다.

 

 

그래서 다른 서비스를 이용하지 않고 구글 고유의 API를 이용해서 데이터를 API로 가져오는 방법을 소개하고자 한다.

 

간단한 정적 사이트를 만들려고 하고, 그 정적 사이트에 들어갈 데이터는 필요한데 MYSQL 같은 무거운 DB는 굳이 필요없는 경우에 잘 활용할 수 있다.

 

코드 소개

const getSheetData = async () => {
  const sheetId = '구글시트 ID';
  const base = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?`;
  const sheetName = '시트 명';
  const query = encodeURIComponent('Select *')
  const url = `${base}&sheet=${sheetName}&tq=${query}`

  const response = await fetch(url);
  // const data = await response.text().substring(47).slice(0, -2).json();
  data = await response.text();
  parsed = JSON.parse(data.substring(47).slice(0, -2))

  console.log(parsed)
  let items = parsed.table.rows
    .map(({ c }) => cleanRow(c))
    .map(([name, branch, typeMid, typeSmall, address, building, lon, lat]) => ({ name, branch, typeMid, typeSmall, address, building, lon, lat }))

  items.forEach(
    (item) => {
      const card = userCardTemplate.content.cloneNode(true).children[0]
      const header = card.querySelector("[data-header]")
      const body = card.querySelector("[data-body]")
      header.textContent = item.name
      body.textContent = item.typeSmall
      userCardContainer.append(card)
    }
  )

  return items
};


function cleanRow(row) {
  // row = [null,{v:'123'},null,{v:'hello'}]
  function replaceNull(item) {
    if (item == null) {
      return { v: '' }
    }
    return item
  }
  data = row
    .map((item) => replaceNull(item))
    .map((item) => item.v)
  return data
}

users = getSheetData()

 

 

코드 설명 

 

기본적으로 Google Visualization API를 응용한 코드다. Visualization API는 구글 시트의 데이터를 기반으로 구글 차트를 그려주는 API인데, 차트를 그리기 위해는 먼저 데이터가 준비되어 있어야 하기 때문에 이 API를 호출하면 데이터도 받을 수 있다.

하지만 데이터가 정제된 형태로 받아지는 것이 아니기 때문에 약간의 가공은 필요하다.

(위 코드에서는 약간의 가공을 통해 스프레드시트 각 행을 item이라는 객체에 담았다)

 

원본 데이터

 

 

참고

https://asbnotebook.com/fetch-google-spread-sheet-data-using-javascript/