스프레드시트 api append & update php 셀 내용 수정

by nanumi posted Aug 08, 2016


Prev이전 문서

Next다음 문서


크게 작게 위로 아래로 댓글로 가기 인쇄

스프레드시트 api append & update





Google 스프레드 시트 API

읽고 Google 스프레드 시트를 작성합니다.

서비스 : sheets.googleapis.com

모든 URI는 아래를 기준으로합니다 https://sheets.googleapis.com

이 API의 검색 URL입니다 https://sheets.googleapis.com/$discovery/rest?version=v4.

컬렉션 : v4.spreadsheets

batchUpdatePOST /v4/spreadsheets/{spreadsheetId}:batchUpdate 
스프레드 시트에 하나 이상의 업데이트를 적용합니다.
createPOST /v4/spreadsheets 
새로 만든 스프레드 시트를 반환, 스프레드 시트를 작성합니다.
getGET /v4/spreadsheets/{spreadsheetId} 
지정된 ID의 스프레드 시트를 돌려줍니다.

컬렉션 : v4.spreadsheets.sheets

copyToPOST /v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo 
복사 다른 스프레드 시트 스프레드 시트에서 한 장.

컬렉션 : v4.spreadsheets.values

appendPOST /v4/spreadsheets/{spreadsheetId}/values/{range}:append 
스프레드 시트에 값을 추가합니다.
batchGetGET /v4/spreadsheets/{spreadsheetId}/values:batchGet 
스프레드 시트에서 값 중 하나 이상의 범위를 돌려줍니다.
batchUpdatePOST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate 
스프레드 시트의 하나 또는 그 이상의 범위에서 값을 설정합니다.
getGET /v4/spreadsheets/{spreadsheetId}/values/{range} 
스프레드 시트에서 값의 범위를 돌려줍니다.
updatePUT /v4/spreadsheets/{spreadsheetId}/values/{range} 
스프레드 시트의 범위에서 값을 설정합니다.

Method: spreadsheets.values.append

TTP request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append

(This URI uses URI Template syntax.)

Path parameters

Parameter nameTypeDescription
spreadsheetIdstringThe ID of the spreadsheet to update.
rangestringThe A1 notation of a range to search for a logical table of data. Values will be appended after the last row of the table.

Query parameters

Parameter nameTypeDescription
valueInputOptionenum(ValueInputOption)How the input data should be interpreted.
insertDataOptionenum(InsertDataOption)How the input data should be inserted.

Request body

The request body contains an instance of ValueRange.

Response body

If successful, the response body contains data with the following structure:

The response when updating a range of values in a spreadsheet.

JSON representation
  "spreadsheetId": string,
  "tableRange": string,
  "updates": {
Field nameTypeDescription
spreadsheetIdstringThe spreadsheet the updates were applied to.
tableRangestringThe range (in A1 notation) of the table that values are being appended to (before the values were appended). Empty if no table was found.
updatesobject(UpdateValuesResponse)Information about the updates that were applied.


Requires one of the following OAuth scopes:

  • https://www.googleapis.com/auth/drive
  • https://www.googleapis.com/auth/spreadsheets

For more information, see the Auth Guide.


Determines how existing data is changed when new data is input.

Enum valueDescription
OVERWRITEThe new data overwrites existing data in the areas it is written. (Note: adding data to the end of the sheet will still insert new rows or columns so the data can be written.)
INSERT_ROWSRows are inserted for the new data.


GET https://spreadsheets.google.com/feeds/worksheets/key/private/basic


셀의 내용 수정

Change contents of a cell

Cells can be modified in place. Unlike other feeds, cells are not directly added nor deleted. They are fixed in place, based on the dimensions of a given worksheet. To add or remove cells from a worksheet, use the worksheets feed to change the dimension of the worksheet. To empty a cell, simply update its value to be an empty string.




Note: Use HTTP PUT to change the data in a cell, even if it is empty; the use of POST on empty cells is no longer recommended. To clear a cell, send an entry with an empty content element in your PUT request instead of using DELETE.

To modify a cell in a worksheet, start by creating an entry element containing the relevant data, which in this case is a formula:

<entry xmlns="http://www.w3.org/2005/Atom"
<link rel="edit" type="application/atom+xml"
<gs:cell row="2" col="4" inputValue="=SUM(A1:B6)"/>

Find the edit URL for the desired cell by performing a GET request to first find the cell. The edit URL is in the href attribute of alink element which has a rel attribute set to edit.

In the body of the PUT request, place the entry element you created above, using the application/atom+xml content type.

Now, send the PUT request to the appropriate Sheets edit URL retrieved above, (replacing key, worksheetId, and cell with their appropriate values):

PUT https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cell

The new data is placed in the specified cell in the worksheet. If the specified cell contains data already, it is replaced.