구글 앱스 스크립트와 함께 구글 시트 테이블 사용하기

• 들어가기 전에

• 개요

• 현재 제한 사항

• 해결 방법

• 마치며

들어가기 전에

Google 시트의 새로운 표 기능은 데이터 정리를 향상시키지만 앱 스크립트를 통해 직접 관리하는 기능은 부족합니다. 기본 지원이 제공될 때까지 구글 앱스 스크립트(Google Apps Script)를 사용하여 해결하는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

---

이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: Workaround: Using Google Sheets Tables with Google Apps Scrip
• URL: https://medium.com/google-cloud/workaround-using-google-sheets-tables-with-google-apps-script-7558bcb1b675

---

"최고 가성비"의 필수 IT 활용 정보 받는 법

엑셀러 권현욱의 20년 "영업비밀" 대공개

Microsoft Excel MVP가 알려주는 노하우 전수받기

---

 

개요

Google 스프레드시트는 최근 표(Tables)라는 새로운 기능을 도입했습니다. 참조 테이블은 자동 머리글, 필터링 옵션 및 데이터 유효성 검사 기능을 통해 형식이 지정되지 않은 범위를 구조화된 데이터 집합으로 변환하여 데이터를 구성하고 관리하는 강력한 방법을 제공합니다. 이렇게 하면 스프레드시트의 가독성과 유지관리가 향상될 뿐만 아니라 기존 Google 스프레드시트 기능과 원활하게 통합할 수 있습니다.

하지만 현재 한 가지 한계가 있습니다. 표는 상당한 이점을 제공하지만 현재 Google Apps Script에는 표를 직접 관리할 수 있는 기본 제공 방법이 없습니다. 여기서는 Google Apps Script를 활용한 해결 방법을 제안함으로써 이러한 격차를 해소합니다. 이 해결 방법은 일시적일 수 있으며, 향후 Google Apps Script에 대한 업데이트를 통해 테이블 관리에 대한 기본 지원이 도입될 수 있습니다.

 

이미지: medium

 

현재 제한 사항

구글 앱스 스크립트 및 구글 스프레드시트 표

• 직접 관리: 현재 구글 앱스 스크립트에는 구글 스프레드시트 표의 메타데이터를 직접 생성하고 수정할 수 있는 기본 제공 방법이 없습니다. 하지만 구글 앱스 스크립트를 사용하여 기존 표 내의 데이터에 액세스하고 조작할 수 있습니다.
• 표 만들기: Google 앱스 스크립트에서는 새 표를 직접 만들 수 없습니다. 표는 Google 스프레드시트 내에서 수동으로 만들어야 합니다.

 

구글 스프레드시트 함수 및 표

• 직접 액세스: Google 스프레드시트 함수를 사용할 때 이름으로 표를 직접 참조할 수 있습니다.
• 표 이름 검색: Google 앱 스크립트를 사용하여 스프레드시트에 있는 모든 표의 이름을 직접 검색할 수 있는 방법은 없다는 점에 유의하세요. 스크립트 내에서 테이블에 액세스할 때 테이블 이름을 수동으로 지정해야 합니다.

 

해결 방법

이 해결 방법에서는 구글 스프레드시트 함수와 Google Apps Script를 결합하여 표 데이터에 액세스하려고 시도합니다. 이 접근 방식을 사용하면 두 도구의 기능을 모두 활용하여 표에서 데이터를 동적으로 검색하고 조작(manipulate)할 수 있습니다.

구글 스프레드시트의 샘플 표

샘플 테이블은 다음과 같습니다. 이 테이블을 'Sheet1'에 넣는다고 가정합니다.

 

이미지: medium

 

테이블 이름을 사용하여 값 검색하기

위 이미지에 표시된 테이블 이름을 사용하여 SampleTable1의 값을 검색하는 샘플 수식은 다음과 같습니다.

=LET(
  tableName, SampleTable1,
  ARRAYFORMULA(OFFSET(tableName, -1,0,ROWS(tableName)+1))
)

 

=ARRAYFORMULA(SampleTable1)을 사용하는 경우 머리글 행은 포함되지 않습니다. 이 수식은 데이터 행을 원하는 경우에만 유용합니다. 그러나 머리글 행을 포함한 전체 테이블을 원할 때는 위의 수식이 유용할 수 있습니다.

Table 이름으로 Google 앱 스크립트를 사용하여 SampleTable1의 값을 검색하는 경우 샘플 스크립트는 다음과 같습니다.

function sample1() {
  const tableName = "SampleTable1";

  const formula = `=LET(
  tableName, ${tableName},
  ARRAYFORMULA(OFFSET(tableName, -1,0,ROWS(tableName)+1))
)`;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.insertSheet(`smaple_${Utilities.getUuid()}`);
  tempSheet.getRange("A1").setFormula(formula);
  SpreadsheetApp.flush();
  const values = tempSheet.getDataRange().getValues(); // Values of the table.
  ss.deleteSheet(tempSheet);
  console.log(values);
}

 

샘플 이미지의 위 표에 이 스크립트를 실행하면 다음과 같은 결과를 얻을 수 있습니다.

[
  ["B2", "C2", "D2", "E2", "F2"],
  ["B3", "C3", "D3", "E3", "F3"],
  ["B4", "C4", "D4", "E4", "F4"],
  ["B5", "C5", "D5", "E5", "F5"],
  ["B6", "C6", "D6", "E6", "F6"]
]

 

테이블 이름을 사용하여 테이블의 A1Notation 검색하기

위 이미지에 표시된 예제에서 테이블 이름을 사용하여 SampleTable2의 A1Notation을 검색하는 예제 수식은 다음과 같습니다(이 샘플 수식은 더 간단할 수 있을 것으로 생각합니다).

 

=LET(
  tableName, SampleTable2,
  regexText, "'?([^']+)'?!",
  tableAdress, CELL("address", tableName),
  checkAddress, REGEXMATCH(CELL("address",tableName),regexText),
  sheetName, IF(checkAddress,REGEXEXTRACT(tableAdress,"'?([^']+)'?!"),""),
  rowPos, ROW(tableName) - 1,
  colPos, COLUMN(tableName),
  rowLength, ROWS(tableName) + 1,
  colLength, COLUMNS(tableName),
  a1Notation1, ADDRESS(rowPos,colPos,4),
  a1Notation2, ADDRESS(rowPos + rowLength - 1,colPos + colLength - 1,4),
  IF(sheetName<>"", "'"&sheetName&"'!"&a1Notation1&":"&a1Notation2, a1Notation1&":"&a1Notation2)
)

 

위의 표를 'Sheet1'에 넣고 'Sheet2'에서 이 수식을 실행하면 다음과 같은 결과를 얻을 수 있습니다

'Sheet1'!B9:H13

이 경우 테이블과 동일한 시트에서 수식을 실행하면 'CELL'이라는 지정으로 인해 시트 이름이 포함되지 않습니다. 또한 이 A1Notation을 사용하면 Google Apps 스크립트를 사용하여 테이블의 값을 검색할 수 있습니다. 샘플 스크립트는 다음과 같습니다.

function sample2() {
  const tableName = "SampleTable2";

  const formula = `=LET(
  tableName, ${tableName},
  regexText, "'?([^']+)'?!",
  tableAdress, CELL("address", tableName),
  checkAddress, REGEXMATCH(CELL("address",tableName),regexText),
  sheetName, IF(checkAddress,REGEXEXTRACT(tableAdress,"'?([^']+)'?!"),""),
  rowPos, ROW(tableName) - 1,
  colPos, COLUMN(tableName),
  rowLength, ROWS(tableName) + 1,
  colLength, COLUMNS(tableName),
  a1Notation1, ADDRESS(rowPos,colPos,4),
  a1Notation2, ADDRESS(rowPos + rowLength - 1,colPos + colLength - 1,4),
  IF(sheetName<>"", "'"&sheetName&"'!"&a1Notation1&":"&a1Notation2, a1Notation1&":"&a1Notation2)
)`;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tempSheet = ss.insertSheet(`smaple_${Utilities.getUuid()}`);
  const range = tempSheet.getRange("A1");
  range.setFormula(formula);
  SpreadsheetApp.flush();
  const a1Notation = range.getValue(); // A1Notation of the table.
  ss.deleteSheet(tempSheet);
  const values = ss.getRange(a1Notation).getValues(); // Values of the table.
  console.log(values);
}

 

샘플 이미지의 위 표에 이 스크립트를 실행하면 다음과 같은 결과를 얻을 수 있습니다.

[
  ["B9", "C9", "D9", "E9", "F9", "G9", "H9"],
  ["B10", "C10", "D10", "E10", "F10", "G10", "H10"],
  ["B11", "C11", "D11", "E11", "F11", "G11", "H11"],
  ["B12", "C12", "D12", "E12", "F12", "G12", "H12"],
  ["B13", "C13", "D13", "E13", "F13", "G13", "H13"]
]

 

표의 A1Notation을 알 수 있는 경우 Google 앱 스크립트를 사용하여 표의 값을 업데이트할 수도 있습니다. 이것이 중요한 포인트가 될 수 있습니다.

 

마치며

구글 스프레드시트의 테이블에서 테이블 이름에서 데이터 값과 A1Notation을 검색하는 방법을 살펴보았습니다. 현재 Google Apps Script에는 스프레드시트 내의 표를 직접 조작할 수 있는 기능이 없습니다. 하지만 향후 업데이트를 통해 해결될 것으로 예상됩니다.

심혈을 기울여 집필한 책들입니다. '영업 비밀'을 공개합니다.

[최고] 가성비로 [최신] Office 활용 정보를 "수시로" 보내드립니다.

---