Overwrite google sheet with new data

问题:

We are using Google Sheets as a data source for Data Studio and other BI products (Tableau, Domo). We create new sheets using the Google Drive API and set the mime type to have the CSV file converted to a Sheet automatically. That all works fine. But when we overwrite an existing sheet with newer data through the Drive API, it deletes the original worksheet and creates a new one (not the sheet itself but rather the one and only worksheet in the sheet). This breaks the connection to the data source for Data Studio (it needs the worksheet ID to stay the same). How do we do the same thing using the Google Sheets API? Is this the strategy?

1. Truncate the data in the existing worksheet (batchClear).
2. Write new data to the existing worksheet starting at cell 0,0 (batchUpdate) and writing X rows at a time in a loop (no file upload through the Sheets API...).

The new data has the same headers but may have fewer rows so just overwriting without clearing will not work. But if there is a way to do this all as one batchUpdate, please let me know.

回答1:

Answered my own question. It is easy enough to overwrite a sheet with new data in one batchRequest call. You just need to send along an UpdateSheetPropertiesRequest that identifies the sheet and sets the new column and row count. Batch that together with an UpdateCellsRequest that has the new data. Here is some groovy code to do just that:

// read CSV file into memory as list of RowData records
def numCols, numRows, rows
(numCols, numRows, rows) = makeRows(url)
// overwrite sheet with data
def requests = []
// update sheet properties
requests << new Request()
  .setUpdateSheetProperties(new UpdateSheetPropertiesRequest()
    .setFields('gridProperties(rowCount,columnCount)')
    .setProperties(new SheetProperties()
      .setSheetId(sheetId)
      .setGridProperties(new GridProperties()
        .setColumnCount(numCols)
        .setRowCount(numRows))))
// overwrite sheet data
requests << new Request()
  .setUpdateCells(new UpdateCellsRequest()
    .setStart(new GridCoordinate()
      .setColumnIndex(0)
      .setRowIndex(0)
      .setSheetId(sheetId))
    .setRows(rows)
    .setFields('*'))
// batch those requests
def body = new BatchUpdateSpreadsheetRequest()
  .setRequests(requests)
def resp = sheets.spreadsheets()
                 .batchUpdate(id, body)
                 .execute()