Why am I getting an "Unable to parse range" error in the Google Sheet Upsert step?

[user-1]

This topic was automatically generated from Slack. You can find the original thread here.

Hi everyone! I’m getting a error on google sheet upsert step:

Error
Unable to parse range: tmp!#ERROR!:#ERROR!

DETAILS
    at Gaxios._request (/tmp/__pdg__/dist/code/021035a6bb454b1c3cef29209abbac28b16fb0268d29e88192565ce11e11286a/node_modules/.pnpm/gaxios@5.1.3/node_modules/gaxios/build/src/gaxios.js:140:23)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at OAuth2Client.requestAsync (/tmp/__pdg__/dist/code/021035a6bb454b1c3cef29209abbac28b16fb0268d29e88192565ce11e11286a/node_modules/.pnpm/google-auth-library@8.9.0/node_modules/google-auth-library/build/src/auth/oauth2client.js:382:18)
    at Object.updateSpreadsheet (file:///tmp/__pdg__/dist/code/021035a6bb454b1c3cef29209abbac28b16fb0268d29e88192565ce11e11286a/code/google_sheets/google_sheets.app.mjs:332:15)
    at Object.updateRow (file:///tmp/__pdg__/dist/code/021035a6bb454b1c3cef29209abbac28b16fb0268d29e88192565ce11e11286a/code/google_sheets/google_sheets.app.mjs:491:14)
    at Function.all (null:null:null)
    at Object.run (file:///tmp/__pdg__/dist/code/021035a6bb454b1c3cef29209abbac28b16fb0268d29e88192565ce11e11286a/code/google_sheets/actions/upsert-row/upsert-row.mjs:181:9)
    at null.executeComponent (/var/task/launch_worker.js:229:22)
    at MessagePort.messageHandler (/var/task/launch_worker.js:726:28)

[user-1]

Hi Jorge,

Please double check the range you’re inputting into the step. That error must likely means that the sheet is misnamed, or the range isn’t editable for some reason.

Here’s a good StackOverflow question that has some answers that might work for you:

Google spreadsheet API, 400 error bad request : unable to parse range

[user-1]

Thank you Pierce, I can’t find the problem.

Do you have some sample workflow with working upsert_row step?

[user-1]

Yes, you can take a look at our Quickstart guide here that uses Google Sheets to save data: Quickstart

[user-1]

that example are using the add_single_row. Add_single_row and update_row are working to me. My problem is only with upsert_row step.

[user-1]

I just tried a basic test and it worked, which makes me think it’s an issue with the step configuration / input values. Can you share more about the step configuration?

[user-1]

hi ,

[user-1]

same thing

[user-1]

Hm that tells me maybe there’s an issue with the spreadsheet. Can you try with a new spreadsheet?

[user-1]

Or potentially an issue with the account connection / access to the account?

[user-1]

maybe some issue related to my location?

[user-1]

I don’t think so

[giovacalle.dev (Giova)]

Hello there, I don’t know if can be useful but i had same problem and i solved as below:

import { axios } from "@pipedream/platform"

export default defineComponent({
  props: {
    google_sheets: {
      type: "app",
      app: "google_sheets",
    }
  },
  async run({steps, $}) {
    const url = new URL(`https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}/values/{RANGE}`);
    url.searchParams.append("valueInputOption", "USER_ENTERED");

    if (IS_UPDATE) {
      return await axios($, {
        url: url.href,
        method: 'PUT',
        headers: {
          Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
        },
        data: {
          values: [
            ARRAY_WITH_DATA
          ]
        }
      });
    } else {
      url.pathname = `${url.pathname}:append`;
      url.searchParams.append("insertDataOption", "OVERWRITE");
      return await axios($, {
        url: url.href,
        method: 'POST',
        headers: {
          Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
        },
        data: {
          range: RANGE,
          majorDimension: "ROWS",
          values: [
            ARRAY_WITH_DATA
          ]
        }
      });
    }
  },
})

In order to give a little of context:

• the step is made using ‘Google Sheets API in Node.js’
• it provides both update and insert (upsert)
• in the code I put some placeholders:
  • IS_UPDATE
  • SPREADSHEET_ID
  • RANGE
  • ARRAY_WITH_DATA
    (hypothetically you can make that these data comes from other steps, as I did indeed)

more info here (in google docs)