How to use Node.js to find Spotify TrackID using Trackname from Google Sheet?

Help
1

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

Hi I’m not understanding how to use nodejs to find TrackID on Spotify using Trackname recorded by google sheet.
Hope everyone can help me

2

Hi I’m not understanding how to use nodejs to find TrackID on Spotify using Trackname recorded by google sheet.
Hope everyone can help me

3

To find a TrackID on Spotify using the Trackname from a Google Sheet, you can follow these steps: 1. Use the Google Sheets API to read the track name from the sheet. 2. Use the Spotify API to search for the track and get the TrackID. First, you’ll need to set up a Pipedream workflow with a trigger (e.g., an HTTP trigger or a scheduled trigger). Then, add a code step to read the track name from the Google Sheet and search for the track on Spotify. Here’s an example Pipedream component that reads the track name from a Google Sheet and searches for the track on Spotify: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spotify: { type: "app", app: "spotify", }, sheetId: { type: "string", label: "Google Sheet ID", }, range: { type: "string", label: "Range", description: "The range of cells to read, e.g. 'Sheet1!A1:A1'", }, }, async run({ $ }) { // Read track name from Google Sheet const sheetResponse = await axios($, { method: "GET", url: `https://sheets.googleapis.com/v4/spreadsheets/${this.sheetId}/values/${this.range}`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); const trackName = sheetResponse.values[0][0]; // Search for the track on Spotify const spotifyResponse = await axios($, { method: "GET", url: `https://api.spotify.com/v1/search`, headers: { Authorization: `Bearer ${this.spotify.$auth.oauth_access_token}`, }, params: { q: trackName, type: "track", limit: 1, }, }); const trackId = spotifyResponse.tracks.items[0].id; return { trackId }; }, }); Replace the sheetId and range props with the appropriate values for your Google Sheet. This component will read the track name from the specified cell and search for the track on Spotify, returning the TrackID.

4

That’s close but can you rewrite that code to pull data using the Google Sheets app?

5

Certainly! Here’s the updated code using the Google Sheets app to read the track name from the Google Sheet and search for the track on Spotify: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { googleSheets: { type: "app", app: "google_sheets", }, spotify: { type: "app", app: "spotify", }, sheetId: { type: "string", label: "Google Sheet ID", }, range: { type: "string", label: "Range", description: "The range of cells to read, e.g. 'Sheet1!A1:A1'", }, }, async run({ $ }) { // Read track name from Google Sheet const sheetResponse = await this.googleSheets.getSpreadsheetValues({ spreadsheetId: this.sheetId, range: this.range, }); const trackName = sheetResponse.values[0][0]; // Search for the track on Spotify const spotifyResponse = await axios($, { method: "GET", url: `https://api.spotify.com/v1/search`, headers: { Authorization: `Bearer ${this.spotify.$auth.oauth_access_token}`, }, params: { q: trackName, type: "track", limit: 1, }, }); const trackId = spotifyResponse.tracks.items[0].id; return { trackId }; }, }); Replace the sheetId and range props with the appropriate values for your Google Sheet. This component will read the track name from the specified cell and search for the track on Spotify, returning the TrackID.

6

I’m already running the code and return this
TypeError
Request path contains unescaped characters

7

I apologize for the issue you’re facing. It seems that there might be some unescaped characters in the URL or parameters of the API request. Let’s update the code to properly encode the URL and parameters. Here’s the updated code: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { googleSheets: { type: "app", app: "google_sheets", }, spotify: { type: "app", app: "spotify", }, sheetId: { type: "string", label: "Google Sheet ID", }, range: { type: "string", label: "Range", description: "The range of cells to read, e.g. 'Sheet1!A1:A1'", }, }, async run({ $ }) { // Read track name from Google Sheet const sheetResponse = await this.googleSheets.getSpreadsheetValues({ spreadsheetId: this.sheetId, range: this.range, }); const trackName = sheetResponse.values[0][0]; // Search for the track on Spotify const spotifyResponse = await axios($, { method: "GET", url: `https://api.spotify.com/v1/search`, headers: { Authorization: `Bearer ${this.spotify.$auth.oauth_access_token}`, }, params: { q: encodeURIComponent(trackName), type: "track", limit: 1, }, }); const trackId = spotifyResponse.tracks.items[0].id; return { trackId }; }, }); I’ve added encodeURIComponent(trackName) to properly encode the track name before making the API request. Please try running this updated code and let me know if you still encounter any issues.

8

it’s return
this.googleSheets.getSpreadsheetValues is not a function
check your code please

9

I apologize for the confusion in my previous response. It seems I made a mistake in the code. Here’s the corrected version using the Google Sheets app’s getValues() method: ```
import { axios } from “@pipedream/platform”; export default defineComponent({ props: { googleSheets: { type: “app”, app: “google_sheets”, }, spotify: { type: “app”, app: “spotify”, }, sheetId: { type: “string”, label: “Google Sheet ID”, }, range: { type: “string”, label: “Range”, description: “The range of cells to read, e.g. ‘Sheet1!A1:A1’”, }, }, async run({ $ }) { // Read track name from Google Sheet const sheetResponse = await this.googleSheets.getValues({ spreadsheetId: this.sheetId, range: this.range, }); const trackName = sheetResponse.values[0][0]; // Search for the track on Spotify const spotifyResponse = await axios($, { method: “GET”, url: https://api.spotify.com/v1/search, headers: { Authorization: Bearer ${this.spotify.$auth.oauth_access_token}, }, params: { q: encodeURIComponent(trackName), type: “track”, limit: 1, }, }); const trackId = spotifyResponse.data.tracks.items[0].id; return { trackId }; }, });

10

same problem it’s said this path is not a function

// Read track name from Google Sheet
    const sheetResponse = await this.googleSheets.getValues({
      spreadsheetId: this.sheetId,
      range: this.range,
    });
11

I apologize for the confusion in my previous response. I made a mistake in the method name. The correct method to use is getSheetValues. Here’s the updated code: ```
import { axios } from “@pipedream/platform”; export default defineComponent({ props: { googleSheets: { type: “app”, app: “google_sheets”, }, spotify: { type: “app”, app: “spotify”, }, sheetId: { type: “string”, label: “Google Sheet ID”, }, range: { type: “string”, label: “Range”, description: “The range of cells to read, e.g. ‘Sheet1!A1:A1’”, }, }, async run({ $ }) { // Read track name from Google Sheet const sheetResponse = await this.googleSheets.getSheetValues({ spreadsheetId: this.sheetId, range: this.range, }); const trackName = sheetResponse.values[0][0]; // Search for the track on Spotify const spotifyResponse = await axios($, { method: “GET”, url: https://api.spotify.com/v1/search, headers: { Authorization: Bearer ${this.spotify.$auth.oauth_access_token}, }, params: { q: encodeURIComponent(trackName), type: “track”, limit: 1, }, }); const trackId = spotifyResponse.data.tracks.items[0].id; return { trackId }; }, });

12

change to python please

13

I apologize for the confusion earlier. Here’s the Python code equivalent for the Pipedream component: import requests def handler(pd): # Read track name from Google Sheet sheet_id = pd.inputs["sheetId"] sheet_range = pd.inputs["range"] google_sheets_auth_token = pd.inputs["googleSheets"]["$auth"]["oauth_access_token"] sheet_url = f"https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/{sheet_range}" sheet_response = requests.get(sheet_url, headers={"Authorization": f"Bearer {google_sheets_auth_token}"}) sheet_data = sheet_response.json() track_name = sheet_data["values"][0][0] # Search for the track on Spotify spotify_auth_token = pd.inputs["spotify"]["$auth"]["oauth_access_token"] spotify_search_url = "https://api.spotify.com/v1/search" spotify_response = requests.get(spotify_search_url, headers={"Authorization": f"Bearer {spotify_auth_token}"}, params={"q": track_name, "type": "track", "limit": 1}) spotify_data = spotify_response.json() track_id = spotify_data["tracks"]["items"][0]["id"] return {"trackId": track_id} Please make sure to add the Google Sheets and Spotify apps as inputs to your Python step in Pipedream, and provide the sheetId and range as inputs as well.

14

turn back to nodejs.
When a new row added to google sheet it’s will trigger an event.
That event will export for me Track Name and Artist Name via this path
steps.trigger.event.newRow
I will search TrackID using TrackName and ArtistName on Spotify using NodeJS

15

To achieve this, you can use the Google Sheets “New Row” trigger and the Spotify “Search” API. Here’s a Pipedream component that searches for a track on Spotify using the Track Name and Artist Name from a new row in a Google Sheet: ```
import { axios } from “@pipedream/platform”; export default defineComponent({ props: { googleSheets: { type: “app”, app: “google_sheets”, }, spotify: { type: “app”, app: “spotify”, }, }, async run({ steps, $ }) { // Get Track Name and Artist Name from newRow event const trackName = steps.trigger.event.newRow.TrackName; const artistName = steps.trigger.event.newRow.ArtistName; // Search for the track on Spotify const query = ${encodeURIComponent(trackName)} artist:${encodeURIComponent(artistName)}; const spotifyResponse = await axios($, { method: “GET”, url: https://api.spotify.com/v1/search, headers: { Authorization: Bearer ${this.spotify.$auth.oauth_access_token}, }, params: { q: query, type: “track”, limit: 1, }, }); const trackId = spotifyResponse.data.tracks.items[0].id; return { trackId }; }, });

16

pipedream said
Cannot read property ‘track’ of undefined
inside the code

17

I apologize for the issue you encountered. It seems that the response from the Spotify API might not contain the expected data. To handle this case, we can add a check to ensure the data exists before accessing the track property. Here’s the updated Node.js code: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { googleSheets: { type: "app", app: "google_sheets", }, spotify: { type: "app", app: "spotify", }, }, async run({ steps, $ }) { const trackName = steps.trigger.event.newRow.TrackName; const artistName = steps.trigger.event.newRow.ArtistName; const query = `${encodeURIComponent(trackName)} artist:${encodeURIComponent(artistName)}`; const spotifyResponse = await axios($, { method: "GET", url: `https://api.spotify.com/v1/search`, headers: { Authorization: `Bearer ${this.spotify.$auth.oauth_access_token}`, }, params: { q: query, type: "track", limit: 1, }, }); if (spotifyResponse.data.tracks && spotifyResponse.data.tracks.items.length > 0) { const trackId = spotifyResponse.data.tracks.items[0].id; return { trackId }; } else { return { error: "No track found for the given Track Name and Artist Name" }; } }, }); This code checks if the tracks property exists and if there are any items in the items array before accessing the track property. If no track is found, it returns an error message.