The problem: Couldn't read cache at: .cache Couldn't write token to cache at: .cache Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=xxxxxxxx-pbeo8ef7fa75gs13ni974ri6bani625j.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fgmail.send&state=7mtlZRVgBqmADjlclRNd12ajvE3oY2&prompt=consent&access_type=offline Enter the authorization code:
I can't enter authorization code because it's written in Logs.
My code: import sys sys.path.append('/home/phonkbase/.local/lib/python3.10/site-packages/') import gspread from oauth2client.service_account import ServiceAccountCredentials from spotipy import Spotify from spotipy.oauth2 import SpotifyClientCredentials from datetime import datetime, timedelta from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from googleapiclient.discovery import build from googleapiclient.errors import HttpError from email.mime.text import MIMEText import base64 import os import pickle
Setting up access to Google Sheets API
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] creds = ServiceAccountCredentials.from_json_keyfile_name('/home/phonkbase/krush/client_secret.json', scope, token_uri='https://oauth2.googleapis.com/token') client = gspread.authorize(creds)
Opening the spreadsheet by name
sheet = client.open("FUNKED UP").sheet1 mails_sheet = client.open("mails Funked Up").sheet1
Setting up access to Spotify API
sp = Spotify(client_credentials_manager=SpotifyClientCredentials(client_id="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", client_secret="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", cache_handler=None))
Setting up access to Gmail API
def get_gmail_service(): creds = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( '/home/phonkbase/krush/client_secret9.json', ['https://www.googleapis.com/auth/gmail.send'] ) creds = flow.run_console() with open('token.pickle', 'wb') as token: pickle.dump(creds, token) return build('gmail', 'v1', credentials=creds)
Function to send an email notification
def send_email(subject, to, message_text): service = get_gmail_service() message = MIMEText(message_text) message['to'] = to message['subject'] = subject raw_message = base64.urlsafe_b64encode(message.as_bytes()).decode() try: message = service.users().messages().send(userId='me', body={'raw': raw_message}).execute() print(f'Message Id: {message["id"]}') except HttpError as error: print(f'An error occurred: {error}')
Getting Spotify playlist data
def get_spotify_playlist_tracks(playlist_id): results = sp.playlist_tracks(playlist_id, fields='items(added_at,track(name)),next') tracks = results['items'] while results.get('next'): results = sp.next(results) tracks.extend(results['items']) return tracks
Getting a list of email addresses from the "mails" table
def get_email_addresses(): emails = mails_sheet.col_values(1)[1:] # Skipping the header return emails
Function to add tracks to Google Sheet
def add_tracks_to_sheet(playlist_id): # Clearing the sheet, starting from the second row, before adding new data sheet.resize(rows=1) # Deleting everything except the first row
# Checking if there are at least two rows in the sheet
if sheet.row_count < 2:
# If not, add an empty row
sheet.add_rows(1)
tracks = get_spotify_playlist_tracks(playlist_id)
email_addresses = get_email_addresses()
rows = []
for i, item in enumerate(tracks[:100]): # Limit to 100 tracks
track = item['track']
added_at = item['added_at'] # Date the track was added to the playlist
added_date = datetime.strptime(added_at.split('T')[0], '%Y-%m-%d')
availability_date = added_date + timedelta(days=30)
today = datetime.now()
if today >= availability_date:
status = 'available'
else:
days_left = (availability_date - today).days
status = f'available in {days_left} days'
# If there is 1 day left until the end of the 30 days, send an email notification
if days_left == 1:
# Send a notification to the address that corresponds to the track number in the playlist
if i-1 < len(email_addresses): # Check if there is an email address for this track
send_email(f'(FUNKED UP) Place {i + 1} "{track["name"]}" notification',
email_addresses[i-1],
f'Before the end of the 30 days of the tracks arrival "{track["name"]}" in the playlist (FUNKED UP) (https://open.spotify.com/playlist/xxxxxx) remains 1 day.')
# Also send a notification to the address "phonkbase1@gmail.com"
send_email(f'(FUNKED UP) Place {i + 1} "{track["name"]}" notification',
"phonkbase1@gmail.com",
f'Before the end of the 30 days of the tracks arrival "{track["name"]}" in the playlist (FUNKED UP) (https://open.spotify.com/playlist/xxxxxx) remains 1 day.')
# In the third column, we display the date of addition + 30 days and the availability status
rows.append([i + 1, track['name'], f'{availability_date.strftime("%Y-%m-%d")} ({status})'])
# Add all rows to the end of the table
sheet.append_rows(rows, value_input_option='USER_ENTERED')
Your Spotify playlist ID
playlist_id = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Running the function to add tracks to the sheet
add_tracks_to_sheet(playlist_id)
