How to use the Google Sheets API for a spreadsheet on a Shared Drive?

Help
, , , ,
1

Hi All,

As part of a Workflow I am extracting data from APIs, constructing a table with multiple rows and writing the table out to Google Sheets. To simplify things – and to allow the Workflow to be idempotent – I am also deleting the previous contents of the table before overwriting it.

I have a cell in my spreadsheet containing the formula =ROWS(C:C), referencing the table, so I know which rows I need to delete – and can obtain this value in the Workflow using the get_values_in_range Action. I’ve managed to craft the proper Google Sheets API request – a batchUpdate containing a DeleteDimension request – and have submitted it, with the correct spreadsheetId and sheetId, to the correct HTTP endpoint.

Here is the code I have developed so far:

import { axios } from "@pipedream/platform"

const driveId       = "*******************"; // Need to use this somehow!
const spreadsheetId = "********************************************";
const sheetId       = 1234567890;

// Helper function to build the batchUpdate body containing a single DeleteDimension request
function build_http_body(sheetId, endIndex){
  return `{  
  "requests": [{
      "deleteDimension": {
        "range": {
          "sheetId": ${sheetId},
          "dimension": "ROWS",
          "startIndex": 1,
          "endIndex": ${endIndex}
        }
      }
  }]
}`
}

export default defineComponent({
  props: {
    google_sheets: {
      type: "app",
      app: "google_sheets",
    },
  },
  async run({steps, $}) {
    // Get the total number of rows in the table from the previous step
    var endIndex = parseInt(steps.get_values_in_range.$return_value[0][0]);
    return await axios($, {
      url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`,
      headers: {
        Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
      },
      body: build_http_body(sheetId, endIndex)
    })
  },
})

What’s the issue, then? This code returns a 404 Not Found - I assume because the Google Sheet I’m writing to is on a Shared Drive - not My Drive.

QUESTION: (how) can I use the driveId, which I know, (and am happy to hard-code into the script alongside the spreadsheetId and the sheetId) in order that the Google Sheets API can find the correct spreadsheet and connect to it?

EDIT: corrected a typo in the above code – removed unnecessary braces – removed an off-by-one error (Google Sheets are indexed from 0) – also checked that the code works on Google’s APIs Explorer; still getting a 404 Not Found error

Possible workarounds:

  1. use the Google Sheet as a persistent data store; only extract new data from the APIs; don’t delete any data at all - just append the new rows (manually edit the spreadsheet if anything goes wrong);

  2. just delete one row at a time - using the existing delete_row Action - then loop over this as many times as required - sending hundreds of HTTP requests to Google in order to delete the whole table;

  3. submit a PR to Pipedream with a slightly tweaked version of the delete_row Action that will delete multiple rows.

The first two workarounds are clearly lacking in elegance; the third would work - but would have to be repeated every time I wanted to use some new functionality from the Google Sheets API. There has to be a simpler solution!

2

Hi @david-f2875a,

axios uses a data property rather than body to send data in the request body.

Also, it looks like the Google Sheets API’s batchUpdate endpoint accepts requests with the POST method, and axios uses GET by default.

An axios call that looks something like this might work:

    return await axios($, {
      method: "POST",
      url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`,
      headers: {
        Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
      },
      data: {
        "requests": [{
          "deleteDimension": {
            "range": {
              "sheetId": sheetId,
              "dimension": "ROWS",
              "startIndex": 1,
              "endIndex": endIndex,
            },
          },
        }],
      }
    })
3

Thanks @js071!

I had been worried about where the PUT was going to go! Initially, following your changes I got a 400 Bad Request - but this was only because (as a n00b JavaScript programmer) I was passing strings instead of JSON objects / integers.

Here, for future reference, is the final (working) code:

import { axios } from "@pipedream/platform"

const spreadsheetId = "********************************************";
const sheetId       = 1234567890;

export default defineComponent({
  props: {
    google_sheets: {
      type: "app",
      app: "google_sheets",
    },
  },
  async run({steps, $}) {
    var endIndex = parseInt(steps.get_values_in_range.$return_value[0][0]);
    if (endIndex > 1) {
      return await axios($, {
        method: "POST",
        url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`,
        headers: {
          Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
        },
        data: {  
          "requests": [{
            "deleteDimension": {
              "range": {
                "sheetId": sheetId,
                "dimension": "ROWS",
                "startIndex": 1,
                "endIndex": endIndex
              }
            }
          }]
        }
      })
    } else {
      console.log("No data to delete; continuing the Workflow...");
    }
  },
})

Curiously, it doesn’t need a reference to the Shared Drive - or any kind of Google Drive authorisation - because I’ve hardcoded the spreadsheetId into the script, Google figures out the location and authorisation issues in the background. #WIN!

1 Like