Python을 사용한 Google 시트 자동화 | 분석 Vidhya

Google Sheets는 Excel에 가장 인기 있고 널리 사용되는 대안 중 하나이며, 실시간 편집, 버전 제어 및 Google Suite와의 완벽한 통합과 같은 기능을 갖춘 협업 환경을 제공하여 사용자가 Google Docs에서 Google Sheets를 호출하고 Google 작업 영역을 활용할 수 있습니다. 주요 데이터 과학 라이브러리 인 Pandas를 사용하여 프로그래밍 방식으로 Excel 파일로 쉽게로드하고 작업 할 수 있지만 유사한 설정을 Google 시트와 함께 복제 할 수 있습니다. SaaS 오퍼링으로서 Google Sheets 데이터에 액세스하려면 Java, JavaScript, Node.js, PHP, Ruby, Python 및 Google 자체 앱 스크립트를 포함한 다양한 프로그래밍 언어를 지원하는 API를 사용해야합니다. 이 기사는 Python을 사용하여 Google 시트 데이터를 Pandas 데이터 프레임에 효율적으로로드하여 빠른 데이터 변환 및 분석을 가능하게하는 데 중점을 둡니다. 변경이 완료되면 GSPread Python 라이브러리를 사용하여 업데이트 된 데이터를 Google 시트로 다시 푸시하여 Google Sheets API와 상호 작용하기위한 편리한 인터페이스를 제공 할 수 있습니다.

이 기사는 Data Science Blogathon 의 일부로 출판되었습니다 .

목차

• Google 클라우드 프로젝트를 설정하십시오
• gspread 이해
• 연결 및 오프닝 스프레드 시트
• Google 시트 작성 및 관리
• 워크 시트 관리
• 세포 특성
• 새로운 행과 열 추가
• 세포와 범위 값을 가져 오기
• 셀과 범위를 업데이트합니다
• 행과 열 삭제
• 검색 세포
• 포맷 셀
• 명확한 범위의 셀과 워크 시트
• Google API의 제한
• 자주 묻는 질문

Google 클라우드 프로젝트를 설정하십시오

앞에서 언급했듯이 Google Sheets는 SaaS 제품이므로 자동화를위한 추가 단계를 준비해야합니다. 인기있는 클라우드 컴퓨팅 플랫폼 인 GCP (Google Cloud Platform)는 Google 제품과 상호 작용하는 데 도움이되는 다양한 서비스를 제공합니다.

광범위하게 Google Sheets Automation을 시작하려면 다음과 같은 3 단계를 따라야합니다.

Google 클라우드 프로젝트 작성 및 구성

https://console.cloud.google.com/ 으로 가서 무료 계정에 가입하십시오. 왼쪽 상단에서 프로젝트 선택 메뉴를 클릭하고 새 프로젝트를 선택하십시오. 프로젝트 이름을 제공하고 조직을“조직 없음”으로 남겨두고, CREATE를 HIT 및 GCP 프로젝트를 설정하고 있습니다.

API 자격 증명 생성 및 보호

Google Sheets 및 Google Drive API를 활성화하고 서비스 계정을 만들어야합니다. 이 특별한 유형의 계정을 통해 인간의 상호 작용없이 Google 클라우드 리소스에 액세스하고 관리 할 수 ​​있습니다. Google Sheets API를 활성화하려면 상단 검색 창에서 시트를 검색하고“Google Sheets API”를 선택하십시오. 허용을 클릭하면 API 세부 정보 페이지로 리디렉션됩니다. 여기에서 "자격 증명 만들기"를 클릭하면 자격 증명 생성 양식이 열립니다.

선택 상자에서 "응용 프로그램 데이터"를 선택하고 다음을 클릭하십시오. 다음 화면에서 나중에 사용하므로 서비스 계정의 의미있는 이름을 제공하십시오. 그런 다음 "편집기"역할을 선택하십시오. 마지막으로 바닥에서 완료를 클릭하십시오.

마찬가지로 Google Drive API를 활성화 할 수 있습니다. 이 API에 대해 다른 서비스 계정을 만들 필요가 없습니다. 기존 서비스 계정은 두 API에 액세스 할 수 있습니다. 이제 Python 스크립트가 Google 시트에 액세스하기 위해 소비 할 자격 증명 JSON을 다운로드해야합니다. 새로 생성 된 서비스 계정 이메일을 클릭하고 키 탭으로 전환하고 키 추가 버튼을 클릭하여 새 키 옵션 만들기를 선택한 다음 JSON을 선택한 다음 작성하십시오.

Google 시트에 대한 액세스 부여

파이썬 스크립트는 생성 된 자격 증명을 사용하여 Google 시트에 액세스합니다. 그러나 스크립트가 사용할 파일에 수동으로 액세스 할 수 있어야합니다. 이렇게하려면 서비스 계정에 대해 생성 된 이메일 (서비스 계정의 세부 사항 탭에 있음)을 복사하고 해당 이메일을 원하는 파일에 편집기로 추가하십시오.

gspread 이해

GSPread는 Google 시트의 Python API 래퍼입니다. 별도의 클래스 및 액세스 방법으로 Google SheetSapi가 제공하는 많은 기능을 캡슐화합니다. SeyetSapi와의 상호 작용을 쉽게 탐색 할 수 있으며 빠르게 선택할 수 있습니다.

지역 환경에서 라이브러리를 설정하려면 다른 Python 패키지와 마찬가지로 간단한 PIP 명령을 사용할 수 있습니다. 의존성 충돌을 피하기 위해 모범 사례로 별도의 환경에 라이브러리를 설치하십시오.

 PIP 설치 GSPREAD

셀 참조에 대한 빠른 메모

Google 시트의 셀 주소는 두 가지 인기있는 표기법을 사용하여 참조 할 수 있습니다.

• A1 표기법 : 이 셀 참조는 시트 이름, 행 번호 및 열 문자로 구성됩니다.이 참조는 시트 이름을 언급하지 않고 작동하며 단일 셀, 셀 범위 또는 전체 열을 참조 할 수 있습니다.
• 명명 된 범위 : 이것은 Google 시트 전체에서 쉽게 식별하고 단순화 된 참조를 위해 사용자 정의 이름을 가진 정의 된 셀 범위입니다.

연결 및 오프닝 스프레드 시트

필요한 액세스 및 라이브러리를 설정 했으므로 코드를 테스트합시다. 서비스 계정 이메일이 포함 된 스프레드 시트에서 첫 번째 셀에 임의의 텍스트를 입력하십시오. 파이썬 스크립트를 사용 하여이 값을 검색하려고합니다.

우리는 JSON 모듈을 사용하여 자격 증명을로드하고 GSPread의 "service_account_from_dict ()"함수로 전달합니다. 이렇게하면 Google Sheets 클라이언트 객체가 반환 되며이 개체를 사용하여 "Open ()"기능을 사용하여 Google 시트를 열 수 있습니다. 아래 코드를 참조하십시오.

 gspread를 가져옵니다
JSON 수입

Open ( 'creds.json')으로 f :
    자격 증명 = json.load (f)

gc = gspread.service_account_from_dict (자격 증명)

sh = gc.open ( "Articledemo")

제목 이름 대신 Google 시트를 열 수있는 두 가지 다른 방법이 있습니다. 이러한 대체 방법은 Google Workspace에서와 같이 제목 이름 종속성을 제거하면 여러 파일이 동일한 제목을 가질 수 있습니다. 스프레드 시트의 경우 제목이 같은 두 파일이있는 경우 API에서 최신 파일에 액세스 할 수 있습니다. 다음 링크를 진행하는 파일 URL 또는 스프레드 시트의 고유 ID를 사용하여 스프레드 시트에 액세스 할 수 있습니다. 아래는 URL 또는 고유 ID를 통해 스프레드 시트에 액세스하는 코드입니다.

 고유 ID를 통한 ## 액세스
sh = gc.open_by_key ( "1r97twcm0fffnssrh_0fjddg-hcqf5plhbhbhrxu9ptv_q")


## URL을 통한 액세스
sh = gc.open_by_url ( "https://docs.com/spreadsheets/d/1r97twcm0fffnssh_0fjdg-hcqf5plhbhbhrxu9ptv_q/edit?gid=0#gid=0"))))

다음 코드는 시트 앞부분에서 입력 한 값을 읽습니다. 코드 작업은 기사의 후반부에서 설명됩니다.

 print (sh.sheet1.acell ( 'a1'). 값)

이것은 시트의 A1 셀에 존재하는 값을 반환합니다.이 경우 우리의 경우 "Gsheet : 이것은 첫 번째 셀입니다"입니다. 이제 우리는 모두 GSPREAD 라이브러리에 깊이 빠져들고 사용 가능한 모든 옵션을 탐색 할 예정입니다.

참고 : Shvariable은 스프레드 시트 객체를 보유하고 있으며 가이드 전체에 참조됩니다.

Google 시트 작성 및 관리

스프레드 시트가 프로그래밍 방식으로 생성되는 많은 사용 사례가있을 수 있습니다. 하나는 사용자를위한 데이터와 통찰력을 게시 할 수있는 솔루션을 구축 할 수 있습니다. 이와 함께이 시트를 사용자와 직접 공유하고 싶을 수도 있습니다.

• 새 스프레드 시트를 만들려면 gspread 클라이언트의 create () 함수를 사용하십시오. 새 스프레드 시트의 제목을 매개 변수로 전달하고 위치를 지정하려면 folder_id 매개 변수를 사용하십시오.
• 생성 된 새 스프레드 시트는 서비스 계정 사용자 만 액세스 할 수 있습니다. 이는 서비스 계정을 만든 사용자에게도 스프레드 시트가 표시되지 않음을 의미합니다. 이를 위해 스프레드 시트 객체의 "share ()"함수를 사용할 수 있습니다. 이 기능에는 "email_address"(이메일 주소), "perm_type"(권한 유형) 및 "역할"의 3 가지 필수 매개 변수가 필요합니다. 권한 유형은 사용자, 그룹, 도메인 또는 누구나 다음 값을 취할 수 있습니다. 대부분의 사용 사례의 경우 사용자 값이 작동합니다. Perm_type에는 'Reader', 'Commenter', 'Writer', 'FileOrganizer', 'Organizer'및 '소유자'등 허용 가능한 수의 고정 된 수용 값이 있습니다. 세분화 된 수준의 정보를 제공하는 추가 선택적 매개 변수도 있습니다.
  • "알림": 사용자가 공유 된 파일 알림을 가져와야하는 경우 부울 값을 제어 할 수 있습니다.
  • "email_message": 알림 이메일과 함께 메시지를 보내는 문자열 값.

 sh = gc.create ( 'Articledemotest') ## 새 스프레드 시트 생성
sh.share (email_address = '[email protected]', perm_type = 'user', role = 'writer', notify = true, email_message = "이것은 테스트 파일입니다")

워크 시트 관리

모든 스프레드 시트는 워크 시트 모음입니다. 이것에 대한 간단한 비유는 책에 여러 페이지가있는 방법입니다. GSPread를 사용하면 사용자는 새 워크 시트에 액세스, 수정, 삭제 또는 생성 할 수 있습니다. 이러한 각 기능을 살펴 보겠습니다.

워크 시트 선택

스프레드 시트 객체의 워크 시트는 워크 시트 객체의 다음 방법을 사용하여 액세스 할 수 있습니다.

• 인덱스에 의한 액세스 : "get_worksheet ()"함수는 액세스 해야하는 워크 시트의 인덱스를 가져옵니다.
• 제목으로 액세스 : "Worksheet ()"함수는 워크 시트의 제목을 가져옵니다. 워크 시트 타이틀은 본질적으로 고유하므로 두 워크 시트가 동일한 제목을 가질 수 없습니다.
• DOT 표기법 바로 가기에 의한 액세스 : DOT 표기법 단축키는 제목, 인덱스 또는 ID를 제공하지 않고 스프레드 시트의 첫 번째 워크 시트에 액세스 할 수 있습니다.
• 모든 액세스 : "Worksheets ()"함수는 스프레드 시트의 모든 워크 시트를 반환합니다. GSPREAD 워크 시트 객체로 반환합니다. "제목"과 "ID"는이 클래스의 몇 가지 중요한 속성으로, 원하는 워크 시트에 대량 방식으로 액세스하는 데 도움이됩니다.
• ID에 의한 액세스 : 자동화 스크립트를 개발하는 동안 타이틀 대신 워크 시트 ID를 다루는 일부 사용 사례가있을 수 있습니다. 이러한 시나리오에서는 "get_worksheet_by_id ()"기능을 사용할 수 있습니다.

다음은 나열된 모든 방법에 대한 샘플 코드입니다.

 print (sh.get_worksheet (0))
print (sh.worksheet ( "articleworksheet1"))
인쇄 (sh.sheet1)
print (sh.get_worksheet_by_id (0))

인쇄 ( "이제 모든 시트를 가져 오는 ...")

## 모든 워크 시트를 반환합니다
sh.worksheets ()의 ws의 경우 :
    인쇄 (WS)

이 모든 인쇄문은 워크 시트 객체를 반환합니다

새로운 워크 시트 만들기

스프레드 시트의 기존 워크 시트 외에도 동일한 스프레드 시트에서 프로그래밍 방식으로 새로운 워크 시트를 만들 수 있습니다. 이 접근법은 기존 워크 시트에서 데이터를 처리하고 별도의 워크 시트로 결과를 게시 할 때 유용 할 수 있습니다.

새 워크 시트를 만들려면 워크 시트 객체의 "add_worksheet ()"기능을 사용해야합니다. 다음 매개 변수가 필요합니다.

• 제목 : 워크 시트 제목
• 행, 열, 색인 (선택 사항) : 새로 생성 된 워크 시트의 행 및 열 수를 정의해야합니다. "인덱스"매개 변수는 선택 사항이며 워크 시트의 순서를 제어합니다.

아래 코드는 100 행과 20 개의 열이있는 워크 시트를 생성하고 워크 시트를 두 번째 위치에 배치합니다.

 sh.add_worksheet ( 'articleworksheet1.5', rows = 100, cols = 20, index = 1)

그리고 그것은 그것을 두 번째 위치에 배치했습니다 (색인 1)

워크 시트의 이름 바꾸기

워크 시트 객체의 update_title () 함수를 사용하여 워크 시트 타이틀 이름을 바꾸면 새 제목을 매개 변수로 받아 들일 수 있습니다.

 print (sh.worksheet ( "articleworksheet3"). update_title ( "articleworksheet2.5"))

워크 시트 삭제

워크 시트 객체의 다음 기능을 사용하여 스프레드 시트에서 워크 시트를 삭제할 수 있습니다.

• 워크 시트 객체를 사용하여 워크 시트를 삭제합니다. “del_worksheet ()”함수는 워크 시트 객체를 매개 변수로 가져 와서 스프레드 시트에서 워크 시트를 삭제합니다.
• 워크 시트 ID를 사용하여 워크 시트를 삭제합니다. "del_worksheet_by_id () 함수는 워크 시트 ID를 워크 시트를 삭제하기위한 입력으로 사용합니다.

사용하는 기능의 선택은 스크립트가 작성된 사용 사례에 따라 다릅니다. 아래는 두 기능의 사용법을 보여주는 코드 샘플입니다.

 sh.del_worksheet (sh.worksheet ( "articleworksheet2.5"))
sh.del_worksheet_by_id ( '602396579')

세포 특성

우리는 천천히 위에서 아래로 좁히고 워크 시트의 가장 작은 (그리고 가장 중요한) 단위 인 셀에 도달합니다. 셀은 행과 열의 교차점입니다. GSPread 라이브러리의 경우 다음 속성을 보유합니다.

• 행 : 셀의 행 번호
• col : 셀의 열 번호
• 가치 : 셀의 값
• 주소 : A1 표기법에서 셀의 주소

아래 샘플 코드는 셀의 모든 특성에 액세스합니다. 검사를위한 특정 셀은 워크 시트의 셀 기능을 사용하여 반환됩니다.

 samplecell = sh.worksheet ( "articleworksheet1"). cell (row = 1, col = 1)
print ( 'row : {} \ ncolumn : {} \ nvalue : {} \ naddress : {}'. 형식 (samplecell.row, samplecell.col, samplecell.value, samplecell.address)

우리가 도서관의 고차 기능을 다루면이 모든 액세서가 플레이 할 것입니다.

새로운 행과 열 추가

기존 샘플 워크 시트에 새로운 행과 열을 추가 하여이 안내서의 이후 섹션에 대한 데이터를 수행 할 수 있습니다. 작업으로서의 삽입은 GSPread 라이브러리에서 두 가지 방식으로 지원됩니다.

특정 위치에서 삽입

insert_row (), insert_rows () 및 insert_cols () 기능을 사용하여 워크 시트 객체의 함수를 사용하여 특정 위치에 행 또는 열을 삽입 할 수 있습니다. 이 기능을 사용하면 워크 시트의 특정 위치에 행 또는 열을 추가 할 수 있습니다. 기능 세부 사항은 다음과 같습니다.

• insert_row : 함수는 삽입 할 값 목록으로 "값"매개 변수가 필요합니다. 목록의 값 순서는 삽입 된 행의 순서를 결정합니다. 기본값이 1으로 1으로 표시되는 "인덱스"매개 변수는 행 삽입 위치를 지정합니다. "value_input_option"및 "inherit_from_before"와 같은 선택적 매개 변수는 함수가 입력 데이터와 행을 해석하는 방법과 사용자가 UI에서 입력하는 것처럼 데이터를 직접 푸시 해야하는지 여부를 제어합니다.
• insert_rows : 여러 행을 삽입하기 위해 "값"매개 변수의 목록 목록을 가져옵니다. 각 목록은 단일 행 역할을합니다. 내부적으로 이것은 GSPread를 통해 행이 워크 시트에 삽입되는 방식의 실제 구현입니다. "insert_row ()"함수는 "insert_rows ()"함수를 호출하므로 "insert_row ()"함수에 대해 설명 된 모든 매개 변수는 하나의 매개 변수를 제외한 "insert_rows ()"에 맞습니다. insert_row () 함수에서 인덱스 매개 변수를 사용하여 오프셋을 결정하고 insert_rows () 함수에서 행 매개 변수로 지정합니다.
• insert_cols : 이 함수는 "row"에서 "col"로 오프셋에 대한 매개 변수 이름이 변경된 "insert_rows ()"함수의 복제본입니다. 나머지 선택적 매개 변수 기능은 동일하게 유지됩니다.

테이블 범위 후 삽입

이 삽입은 행에만 적용됩니다. 위치가 알려지지 않은 특정 테이블 범위에 행을 삽입 할 수 있습니다. 다시, 삽입은 단일 또는 멀티 로우 방식으로 수행 할 수 있습니다.

• 부록 : "값"매개 변수를 통해 행 값을 목록으로 사용합니다. "table_range"매개 변수는 행 삽입이 발생 해야하는 테이블 범위를 정의하는 데 도움이됩니다. 범위는 A1 표기법으로 제공됩니다.
• 부록 : "insert_rows ()"와 마찬가지로 "insert_rows ()"는 테이블 범위 이후의 행 삽입의 실제 구현입니다. 두 함수에 대한 모든 매개 변수는 "actend_rows ()"가 "값"매개 변수에서 목록 목록을 가져 오는 차이와 동일하게 유지됩니다.

다음은 다음과 같습니다.

• 열에 대한 행을 추가합니다 : A, B, C 및 D
• 이 열에 4 개의 행을 추가합니다

 SampleWorksheet.insert_row (
    [ 'a', 'b', 'c', 'd']]]
))
sampleworksheet.insert_rows (
    [의 뜻
        [ 'kg', 54, 23, 12],
        [ 'Og', 34, 12, 34],
        [ '나', 23, 45, 90],
        [ 'Ye', 65, 12, 54]
    ], 행 = 2
))

이제 다음과 같은 단계를 수행하겠습니다.

• 이 테이블 범위에서 2 행을 계속 부여하십시오
• 다른 열 e를 추가하십시오

 SampleWorksheet.append_rows (
    [의 뜻
        [ 'sn', 67, 87, 45],
        [ 'ar', 56, 23, 65]
    ],,
    table_range = "a1 : d5"
))
SampleWorksheet.insert_cols (
    [의 뜻
        [ 'e', ​​56, 34, 65, 34, 76, 45]
    ],,
    col = 5
))

참고 : SampleWorksheet 변수는 워크 시트 객체를 보유하고 있으며 가이드 전체에 참조됩니다.

세포와 범위 값을 가져 오기

마지막 섹션에서는 다양한 삽입 작업을 사용하여 프로그래밍 방식으로 데이터를 준비했습니다. 이제 다양한 읽기 기능을 사용하여 삽입 된 데이터를 가져올 수 있습니다. 우리는 셀을 가져 오는 방법을보고 다양한 셀과 전체 워크 시트에서 값을 가져 오기 위해 이동합니다.

단일 셀 가져 오기

워크 시트에서 가장 기본적인 읽기 작업은 이전 섹션에 설명 된대로 값 또는 기타 셀 속성을 얻는 것입니다. 단일 셀을 가져 오려면 두 가지 기능이 있습니다.

• ACELL : 이것은 셀 주소를 A1 표기법으로 가져와 셀 객체를 반환합니다.
• 셀 : 이것은 (행, 열) 순서대로 셀 좌표를 취합니다.

이 두 기능 모두 셀 객체를 반환하고 이미 이러한 객체에서 값을 얻는 방법을 보았습니다. Acell 기능은 Google Sheets API와 연결을 설정 한 섹션에서 사용되었습니다.

 print (sampleworksheet.acell ( 'a1'). 행)
print (sampleworksheet.cell (1, 1) .Value)

워크 시트 또는 범위의 모든 셀을 가져옵니다

• 워크 시트 객체의 "get_all_cells ()"기능을 사용하여 셀 객체 목록에서 워크 시트의 모든 셀을 얻을 수 있습니다. 이 기능에는 매개 변수가 없으며 워크 시트 객체에서 직접 호출 할 수 있습니다.
• 특정 범위의 셀 객체를 가져 오려면 워크 시트 객체의 범위 () 함수를 사용하십시오. 이 함수는 A1 표기법, 숫자 경계 또는 명명 된 범위와 같은 다양한 입력 양식을 허용합니다. 입력없이 사용하면 단일 API 호출로 워크 시트의 모든 셀을 반환합니다. 셀 특성과 관련된 사용 사례의 경우이 기능은 원하는 셀을 필터링하고 추가 작업을 수행하는 데 도움이됩니다.

 print (sampleworksheet.get_all_cells ())
print (sampleworksheet.range ( 'b4 : e5'))

세포 범위의 값을 가져옵니다

사용자는 일반적으로 더 나은 접근성을 위해 동일한 워크 시트에서 여러 미니어처 테이블을 만듭니다. 이러한 경우, 우리는이 테이블 범위의 정확한 주소로 가져 오기 범위를 정제해야합니다. 이러한 테이블 범위를 가져 오려면 워크 시트 객체의 다음 두 기능을 사용할 수 있습니다.

• get : "get ()"함수는 A1 표기법 또는 이름의 범위에서 테이블 범위를 취하고 값 목록 목록을 반환합니다.
• batch_get : "get ()"함수는 하나의 범위만을 취할 수 있지만 여러 범위를 다루는 경우 Batch_get을 사용할 수 있습니다. 이 기능은 하나의 API 호출을하여 비용을 절약합니다.

 print ( 'get range : {}'. 형식 (sampleworksheet.get ( "a1 : d4")))
print ( '배치 get 범위 : {}'. 형식 (sampleworksheet.batch_get (
    "A1 : D4",
    "B4 : E3"
])))

행이나 열에서 모든 값을 가져 오기

워크 시트 객체의 "row_values ​​()"및 "col_values ​​()"함수를 사용하여 행 또는 열의 모든 값을 가져올 수 있습니다. 두 기능 모두 행 또는 열의 위치 (1) 위치를 취하고 목록의 값을 반환합니다.

 print (sampleworksheet.row_values ​​(1))
print (sampleworksheet.col_values ​​(4))

전체 워크 시트 값을 가져옵니다

전체 워크 시트를 가져 오는 가장 좋은 사용 사례 중 하나는이 데이터를 Pandas 데이터 프레임에 직접로드 한 다음 요구 사항에 따라 후 처리 또는 분석을 수행하는 것입니다. 전체 데이터는 워크 시트 객체의 다음 기능을 사용하여 반환 할 수 있습니다.

• 목록 목록 : "get_all_values ​​()"함수는 모든 행을 목록으로 반환 한 다음 모든 행을 하나의 목록에 반환합니다. 이 함수는 "get_values ​​()"함수와의 별칭이지만 흥미롭게도 "get_values ​​()"함수는 "get ()"함수를 사용하여 구현됩니다. 입력이없는 "get ()"함수는 목록 목록을 반환합니다. 따라서 3 가지 기능 모두 동일합니다.
• 사전 목록 : "get_all_records ()"함수는 사전 목록을 반환합니다. 각 사전은 키가 1 열 값이고 다음 열 값으로 값인 키 값 매핑입니다. 모든 행은 사전을 얻습니다. 기본적으로 첫 번째 행이 키라고 가정하지만 "헤더"매개 변수를 사용하여 다른 행을 키로 알 수 있습니다. 빈 셀과 예상 헤더를 처리하는 데 도움이되는 몇 가지 추가 매개 변수가 있습니다.

두 기능의 출력을 Pandas Dataframe 함수로 직접 전달하여 워크 시트 테이블을 Pandas Dataframe으로 얻을 수 있습니다.

 팬더를 PD로 가져옵니다
print (pd.dataframe (sampleworksheet.get_all_records ()))
print (pd.dataframe (sampleworksheet.get_all_values ​​()))

셀과 범위를 업데이트합니다

스프레드 시트의 기존 데이터를 업데이트하는 것은 GSPread 라이브러리 기능을 사용하여 쉽게 수행 할 수있는 가장 중요한 기능입니다. 스프레드 시트의 셀, 단일 셀 업데이트를 범위의 여러 셀에 업데이트 한 다음 단일 API 호출로 여러 범위로 업데이트하는 여러 가지 방법이 있습니다.

단일 셀 업데이트

워크 시트 객체의 다음 기능을 사용하여 워크 시트의 단일 셀을 업데이트 할 수 있습니다.

• Update_acell : 이 기능은 A1 표기법의 셀 주소와 업데이트 될 값의 두 매개 변수를 사용합니다.
• update_cell : 이 기능은 셀의 좌표를 행 열차 순서와 업데이트 값으로 가져갑니다.
• 업데이트 : 이 기능은 여러 셀을 업데이트 할 수있는 범위가 더 크지 만 단일 셀을 업데이트하는 데 사용될 수도 있습니다. 입력 매개 변수 순서는 위의 두 기능과 다릅니다. "update ()"함수는 목록 목록을 첫 번째 값으로, 셀 주소로 가져옵니다.

 print (sampleworksheet.update_acell ( 'a2', 'kaustubh')))
print (sampleworksheet.update_acell ( 'a3', 'oggy'))))
print (sampleworksheet.update ([[ 'hello']]], 'a4')))

다양한 셀을 업데이트합니다

워크 시트 객체에서 다음 두 기능을 사용하여 워크 시트의 다양한 셀을 업데이트 할 수 있습니다.

• Update_Cells : 이 기능은 "range ()"함수와 함께 가장 잘 작동합니다. "update_cells ()"함수는 셀 목록으로 입력을 취합니다. 이 셀 목록은 범위 함수에서 반환 된 셀 객체를 반복하여 값 속성에 액세스하여 값을 변경할 수 있습니다.
• 업데이트 : 단일 셀 업데이트에서 이전에 볼 수 있듯이이 기능을 사용하여 이름이 지정된 범위 또는 A1 통합 범위를 업데이트 할 수 있습니다.

 RangeOfCells = SampleWorkSheet.Range ( 'B2 : B7')
범위 셀의 셀의 경우 :
    newValue = int (cell.value) 10
    cell.value = newValue
print (sampleworksheet.update_cells (rangeofcells))

위의 코드는 다양한 셀 범위를 가져오고 값에 10을 추가하고 단일 API 호출로 업데이트합니다.

여러 범위의 셀을 업데이트합니다

위의 섹션에서는 단일 API 호출로 범위에서 여러 셀을 업데이트 할 수있었습니다. 이 동작은 여러 범위로 확장 될 수 있습니다. 즉, 한 번의 호출로 여러 그룹의 셀을 업데이트 할 수 있습니다. "batch_update ()"함수는 키가 범위 및 값으로 키를 가진 사전 목록을 취합니다. 범위 키 값은 A1 표기법 범위 또는 명명 범위 여야하며 값 키 값은 값 목록 목록입니다.

 범위 1 = 'C2 : C7'
범위 2 = 'e2 : e7'
BothRangeValues ​​= SampleWorksheet.batch_get ([
    범위 1,
    범위 2
])))
Range1Values, Range2Values ​​= BothrangeValues
Range1updatedValues ​​= [[int (x [0]) 10] range1Values에서 x의 경우]
Range2updatedValues ​​= [[int (x [0]) 20] Range2Values에서 x에 대한 x]
print (sampleworksheet.batch_update ([
    {
        '범위': 범위 1,
        '값': Range1updatedValues
    },

    {
        '범위': Range2,
        '값': range2updatedValues
    }
])))

위의 코드는 "batch_get ()"함수를 사용하여 두 가지 범위를 가져온 다음 로컬로 값을 업데이트 한 다음 "batch_update ()"함수를 사용하여 업데이트 된 값을 Google 시트로 푸시합니다. 이 업데이트의 출력은 다음과 같습니다.

행과 열 삭제

이 시점까지 워크 시트에 데이터를 삽입, 읽기 및 업데이트했습니다. 워크 시트에서 중복 또는 불필요한 데이터를 제거하기 위해 삭제 작업을 수행 할 수 있습니다. "delete_rows ()"및 "delete_colums ()"함수는 "start_index"를 삭제합니다. "end_index"가 지정되면 시작 및 끝의 인덱스 범위에서 모든 열을 삭제합니다.

 print (sampleworksheet.delete_columns (4))
print (sampleworksheet.delete_rows (6))

검색 세포

Google Sheets API를 사용하면 문자열이나 정규 표현식을 일치시켜 셀을 검색 할 수 있습니다. 대소 문자에 민감하거나 사례에 민감한 검색을 수행하고 원하는 경우 검색을 특정 행이나 열로 좁힐 수 있습니다. 이 두 워크 시트 기능을 사용하여 일치하는 셀을 찾으십시오.

• find : "find ()"함수는 경기의 첫 번째 발생을 반환합니다. 이는 검색 문자열 또는 Regex, "in_row"또는 "in_column"매개 변수를 사용하여 검색 유형을 제어하기 위해 검색 및 "case_sensentitive"플래그를 좁히기 위해 취합니다. "in"매개 변수는 행 또는 열 위치를 취합니다 (색인 1)
• findall : "findall ()"은 검색의 모든 일치를 반환하는 "find ()"함수의 고급 단계입니다.

 가져 오기 re
print (sampleworksheet.find ('64 ', in_column = 2))
searchRe = re.comPile (r '(a | a)')
print (sampleworksheet.findall (searchre))

포맷 셀

Excel에서는 텍스트 하이라이트, 서식, 국경, 정렬 및 수많은 기능을 포함하여 다양한 방식으로 워크 시트를 포맷 할 수 있습니다. Google Sheets는 또한 셀에 대한 다양한 서식 옵션을 제공합니다. 전체 필드 목록은 Google Sheets Cell Documentation 에서 제공됩니다.

gspread 워크 시트 객체의 Format () 함수를 사용하여 형식을 적용 할 셀 또는 범위를 지정할 수 있습니다. 모든 형식의 주요 필드 및 해당 값을 포함하는 JSON 사전으로 형식을 제공하십시오.

아래 코드는 테이블의 모든 셀에 테두리를 적용합니다.

 Borderformatting = {
    "스타일": "Solid",
    "Colorstyle": { "rgbcolor": { "red": 0, "green": 0, "blue": 0, "alpha": 1}},
}

인쇄(
    샘플 worksheet.format (
        "A1 : D6",
        형식 = {
            "국경": {
                "상단": Borderformatting,
                "하단": Borderformatting,
                "왼쪽": Borderformatting,
                "오른쪽": Borderformatting,
            },
        },
    ))
))

배치 형식을 적용하여 동시에 여러 범위를 형식화 할 수 있습니다. 이로 인해 모든 새로운 변화에 대한 다른 형식의 호출을 작성하는 데 많은 시간이 절약됩니다. "batch_format ()"함수는 두 가지 중요한 키를 포함하는 사전 목록을 가져옵니다. 첫 번째 키는 셀의 범위와 서식 사전을 포함하는 형식 키를 정의하는 범위 키입니다.

"batch_format ()"함수를 사용하여 샘플 테이블에서 다음을 수행하겠습니다.

• 테이블의 모든 셀에 경계를 바르십시오.
• 첫 번째 줄의 텍스트를 굵게 표시하여 테이블의 열이라는 것을 나타냅니다.
• 중앙의 모든 데이터를 정렬하십시오.
• 열에 밝은 파란색을 추가하십시오 (첫 번째 행).

 Borderformatting = {
    "스타일": "Solid",
    "Colorstyle": { "rgbcolor": { "red": 0, "green": 0, "blue": 0, "alpha": 1}},
}
형식 = [
    {
        "범위": "A1 : D6",
        "형식": {
            "국경": {
                "상단": Borderformatting,
                "하단": Borderformatting,
                "왼쪽": Borderformatting,
                "오른쪽": Borderformatting,
            },
            "수평선": "센터",
        },
    },
    {
        "범위": "A1 : D1",
        "형식": {
            "TextFormat": {
                "Bold": 사실,
            },
            "BackgroundColorStyle": {
                "rgbcolor": { "빨간색": 0.8, "녹색": 0.8, "blue": 1, "alpha": 0.8}
            },
        },
    },
]]

print (sampleworksheet.batch_format (형식))

그리고 이것은 우리 테이블의 최종 상태입니다.

명확한 범위의 셀과 워크 시트

운영을 수행하기 전에 범위를 지우고 싶을 수도 있습니다. 셀 범위를 지우려면 워크 시트 객체의 "batch_clear ()"기능을 사용합니다. 이것은 청소 해야하는 범위 목록을 취합니다. 샘플 테이블에서 열 C를 지우겠습니다.

 print (sampleworksheet.batch_clear ([ "c1 : c6"])))

참고 : CLEAR 함수는 적용된 서식이 아닌 값을 지우는 것만 지우고 있습니다.

워크 시트 객체의 "Clear ()"기능을 사용하여 전체 워크 시트를 지울 수 있습니다.

 print (sampleworksheet.clear ())

Python을 사용하여 Google 시트를 자동화하는 방법에 대한이 기사를 확인하십시오.

Google API의 제한

GSPread 라이브러리를 사용하여 Google 시트에서 많은 작업을 수행했습니다. 이 라이브러리는 사용자가 통과 한 데이터를 허용 가능한 형식으로 준비하고 Sheets와 관련된 Google 프로젝트에 API 호출을 만드는 래퍼 일뿐입니다. 개발자가 기본 API 호출, 페이로드 및 응답을 이해할 필요가 없도록 작동합니다. 개발자는 추상 기능과 만 상호 작용합니다.

• API 할당량 소비 이해 : 개발자는 API 호출이 특히 생산 및 중요한 작업을 위해 할당량을 소비하는 방법을 이해해야합니다.
• 무료이지만 제한된 사용 : Google Sheets API는 무료이지만 API 호출 수에 제한이 있습니다.
• 일반적인 오류 - 429 ( "너무 많은 요청") : 많은 사용자가 API 통화 한계를 초과하여 429 오류를 만납니다.
• 할당량 제한 예 : 현재 할당량은 프로젝트 당 분당 300 개의 요청을 허용합니다.
• 할당량 초과 : 스크립트가 300 개 이상의 요청을 보내면 추가 요청이 처리되지 않습니다.
• 지수 백 오프 솔루션 : 임의의 대기 시간을 사용하는 지수 백 오프와 같은 재 시도 메커니즘은 이러한 제한을 관리하는 데 도움이 될 수 있습니다.

결론

이 안내서에서는 Google Sheets UI에서 수행 할 모든 작업을 수행하기위한 Google 서비스 계정을 만들었습니다. 데이터 추가, 업데이트 및 삭제와 같은 많은 기능을 탐색했습니다. 또한 Python을 사용하여 시트와 Google 시트 자동화를 포맷하는 방법을 살펴 보았습니다.

Google Sheets API는 셀 병합, 보호 범위 만들기, 셀을 숨기고, 메모 추가, 사본 범위 또는 필터 추가 등 모든 작업을 프로그래밍 방식으로 제공합니다! GSPread 라이브러리에 대한 문서에는 이러한 설명이 부족하지만 Google Sheets 공식 문서를 살펴보고 도서관에서 구현 된 모든 기능에 대한 높은 수준의 정보를 제공하는 GSPread 문서의 API 참조 섹션을 확인할 수 있습니다.

주요 테이크 아웃

• Google Sheets Automation에는 비용이 들지 않는 Google 클라우드 프로젝트를 설정해야합니다. 그러나 코드를 최적화하지 않으면 할당량을 소진하여 코드의 나중에 오류가 발생할 수 있습니다. 가능한 한 배치 기능을 사용하는 것을 목표로합니다.
• Google Sheets API를 사용하여 달성 할 수있는 가능성이 많이 있으며 GSPread 라이브러리는 시작점 일뿐입니다. 라이브러리에 누락 된 기능을 구현하기 위해 공식 문서를 탐색해야합니다.
• Google Sheets 자동화 설정을 사용하여 마스터 레코드를 유지하고 OLAP 데이터베이스와 같이 고급 데이터베이스로 푸시하는 소싱 파이프 라인을 구축 할 수 있습니다. 이 데이터베이스를 Tableau와 같은 BI 소프트웨어에 연결하여 엔드 투 엔드 프로젝트를 완료하십시오.
• Google Sheets 자동화는 데이터를 자동으로 업데이트, 가져 오기 또는 구성하는 워크 플로를 설정하여 효율성을 크게 향상시켜 수동 데이터 입력을 제거하는 데 도움이 될 수 있습니다.
• Google 시트 자동화를 사용하면 보고서 보내기, 계산 수행 또는 셀 업데이트와 같은 반복되는 작업을 예약하여 반복적 인 수동 작업의 필요성을 줄일 수 있습니다.

자주 묻는 질문

Q1. "429 : 너무 많은 요청"오류를 피하기 위해 무엇을 할 수 있습니까?

A. 한 번에 다시 요청을하려는 재 시도 메커니즘이 구현되어야합니다. 이러한 예 중 하나는 지수 백 오프 알고리즘입니다

Q2. Google 시트에서 자동화를 할 수 있습니까?

A. 예, 매크로 , Google Apps 스크립트 및 Zapier 또는 Make (이전의 intembromat) 와 같은 제 3 자 도구와 같은 내장 기능을 사용하여 Google 시트에서 작업을 자동화 할 수 있습니다. 이를 통해 데이터 입력, 서식 및 계산과 같은 반복적 인 작업을 자동화 할 수 있습니다.

Q3. Google에는 자동화 도구가 있습니까?

A. Google은 Google Workspace 앱 (예 : Sheets, Docs, Gmail)에서 작업을 자동화하기위한 JavaScript 기반 플랫폼 인 Google Apps 스크립트를 제공합니다. Additionally, tools like Google Cloud Workflows and Zapier integrations can be used for broader automation across Google services.

Q4. How can I set up Google Sheets automations to save time on repetitive tasks?

A. You can set up Google Sheets automations using built-in tools like Macros and Google Apps Script. Macros allow you to record actions and replay them, while Google Apps Script lets you create custom automations using JavaScript. These automations can streamline repetitive tasks such as data entry, formatting, or running specific functions automatically in Google Sheets.

Q5. What are some popular Google Sheets automations for data analysis?

A. Popular Google Sheets automations for data analysis include automated data import from external sources, scheduled reports using Google Apps Script, and conditional formatting to highlight trends. These automations help optimize the data analysis process, making Google Sheets a powerful tool for managing and interpreting large datasets efficiently.

위 내용은 Python을 사용한 Google 시트 자동화 | 분석 Vidhya의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!