Why am I receiving a "No rows in the Google Sheet" error when running a query using Google Sheet and axios in Pipedream platform?

Help
1

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

:wave: NEED Help :pray:

I am attempting to run a google sheet query using the provided code. I want to return as an export, all of the data in the column below the correct header row. For example, if the lead score is 28, I should get back all of the resources in the 20-40 column. Instead, I am getting an error that states: No rows in the Google Sheet. I have confirmed the doc id and the sheet tab name, however, it still isn’t working.
Here is the google sheet: MortgageResourcesJerryHayes - Google Sheets
Here is my code:
import { axios } from “@pipedream/platform”;

export default defineComponent({
props: {
google: {
type: “app”,
app: “google”,
},
sheetId: {
type: “string”,
label: “Google Sheet ID”,
},
leadScore: {
type: “integer”,
label: “Lead Score”,
},
},
async run({ $ }) {
const sheetData = await axios(this, {
method: “GET”,
url: https://sheets.googleapis.com/v4/spreadsheets/${**this**.sheetId}/values/Jerry!A1:F,
headers: {
Authorization: Bearer ${**this**.google.$auth.oauth_access_token},
},
});

**const** rows = sheetData.data ? sheetData.data.values || [] : [];
**if** (rows.length === 0) {
  console.error("No rows in the Google Sheet");
  **return**;
}
**const** header = rows.shift();
**if** (!header) {
  console.error("Header row is missing in the Google Sheet");
  **return**;
}
**if** (rows.length === 0) {
  console.error("No data rows in the Google Sheet");
  **return**;
}

**let** columnIndex;
**for** (**let** i = 0; i < header.length; i++) {
  **const** range = header[i].split("-");
  **const** min = parseInt(range[0]);
  **const** max = parseInt(range[1]);
  **if** (**this**.leadScore >= min && **this**.leadScore <= max) {
    columnIndex = i;
    **break**;
  }
}

**if** (columnIndex === **undefined**) {
  console.error("The lead score range does not match any column");
  **return**;
}

**const** resources = rows
  .filter((row) => row[columnIndex] !== "")
  .map((row) => row[columnIndex]);

_// Export the resources array_
**this**.$emit(resources, {
  summary: `${resources.length} matching resources found`,
});

},
});

2

HI , I think your question is a great case for Pipedream bot to help. I’ll pass your question to the Pipedream bot now. Please feel free to ask it any question

3

I am attempting to run a google sheet query using the provided code. I want to return as an export, all of the data in the column below the correct header row. For example, if the lead score is 28, I should get back all of the resources in the 20-40 column. Instead, I am getting an error that states: No rows in the Google Sheet. I have confirmed the doc id and the sheet tab name, however, it still isn’t working.
Here is the google sheet: MortgageResourcesJerryHayes - Google Sheets
Here is my code:
import { axios } from “@pipedream/platform”;

export default defineComponent({
props: {
google: {
type: “app”,
app: “google”,
},
sheetId: {
type: “string”,
label: “Google Sheet ID”,
},
leadScore: {
type: “integer”,
label: “Lead Score”,
},
},
async run({ $ }) {
const sheetData = await axios(this, {
method: “GET”,
url: https://sheets.googleapis.com/v4/spreadsheets/${**this**.sheetId}/values/Jerry!A1:F,
headers: {
Authorization: Bearer ${**this**.google.$auth.oauth_access_token},
},
});

**const** rows = sheetData.data ? sheetData.data.values || [] : [];
**if** (rows.length === 0) {
  console.error(“No rows in the Google Sheet”);
  **return**;
}
**const** header = rows.shift();
**if** (!header) {
  console.error(“Header row is missing in the Google Sheet”);
  **return**;
}
**if** (rows.length === 0) {
  console.error(“No data rows in the Google Sheet”);
  **return**;
}

**let** columnIndex;
**for** (**let** i = 0; i < header.length; i++) {
  **const** range = header[i].split(“-”);
  **const** min = parseInt(range[0]);
  **const** max = parseInt(range[1]);
  **if** (**this**.leadScore >= min && **this**.leadScore <= max) {
    columnIndex = i;
    **break**;
  }
}

**if** (columnIndex === **undefined**) {
  console.error(“The lead score range does not match any column”);
  **return**;
}

**const** resources = rows
  .filter((row) => row[columnIndex] !== “”)
  .map((row) => row[columnIndex]);

_// Export the resources array_
**this**.$emit(resources, {
  summary: `${resources.length} matching resources found`,
});

},
});

4

I noticed that you’re using the wrong quotation marks for strings in your code. You should use single quotes (') or double quotes ("), not curly quotes (“ and ”). Here’s the corrected version of your code: ```
import { axios } from “@pipedream/platform”; export default defineComponent({ props: { google: { type: “app”, app: “google”, }, sheetId: { type: “string”, label: “Google Sheet ID”, }, leadScore: { type: “integer”, label: “Lead Score”, }, }, async run({ $ }) { const sheetData = await axios(this, { method: “GET”, url: https://sheets.googleapis.com/v4/spreadsheets/${this.sheetId}/values/Jerry!A1:F, headers: { Authorization: Bearer ${this.google.$auth.oauth_access_token}, }, }); const rows = sheetData.data ? sheetData.data.values || [] : []; if (rows.length === 0) { console.error(“No rows in the Google Sheet”); return; } const header = rows.shift(); if (!header) { console.error(“Header row is missing in the Google Sheet”); return; } if (rows.length === 0) { console.error(“No data rows in the Google Sheet”); return; } let columnIndex; for (let i = 0; i < header.length; i++) { const range = header[i].split(“-”); const min = parseInt(range[0]); const max = parseInt(range[1]); if (this.leadScore >= min && this.leadScore <= max) { columnIndex = i; break; } } if (columnIndex === undefined) { console.error(“The lead score range does not match any column”); return; } const resources = rows .filter((row) => row[columnIndex] !== “”) .map((row) => row[columnIndex]); // Export the resources array this.$emit(resources, { summary: ${resources.length} matching resources found, }); }, });

5

and @U03CXTHK4RF, I still get the same issue of no exports and the logs state that there’s “No rows in the google sheet”.

6

I apologize for the confusion. Let’s try to troubleshoot the issue. First, please double-check that the sheetId prop is set correctly and that you have the correct Google Sheet ID. Also, ensure that your Google Sheet has data in the specified range (Jerry!A1:F). If you’re still experiencing issues, please provide more information about your Google Sheet’s structure and the data you’re trying to retrieve. This will help me better understand the issue and provide a more accurate solution.