getupdatedstudies.py

from google.oauth2 import service_account
import pandas as pd
import gspread
import csv, os, json
from itertools import islice
from boxsdk import Client, OAuth2, CCGAuth
from boxsdk.network.default_network import DefaultNetwork
from io import StringIO

service_account_info = os.getenv("GSHEETS_FILE")

gc = gspread.service_account(filename=service_account_info)

spreadsheet = gc.open_by_url('https://docs.google.com/spreadsheets/d/1wM5CTyLcinqcGejrH928JkVBVUzbhbEyrxYFndh6MnM/edit#gid=0')

worksheet = spreadsheet.get_worksheet(0)
records_data = worksheet.get_all_records(expected_headers=["name of x col.","name of y col.","name of \"cell type\" col.","name of \"unique region\" col"])

with open("remapkeys.json","r") as keys:
    current_keys = json.load(keys)
added = 0
with_files = 0
for study in records_data:
    if len(study["Folder Name"]) > 0 and study["Folder Name"] not in current_keys:
        if all(len(study.get(x,"")) > 0 for x in ["name of x col.","name of y col.","name of \"cell type\" col.","name of \"unique region\" col"]):
            study_dict = {"x":study["name of x col."],"y":study["name of y col."],"region_imaged":study["name of \"unique region\" col"],"cell_type":study["name of \"cell type\" col."]}
            if len(study["Files to process"]) > 0:
                study_dict["files"] = [x.strip() for x in study["Files to process"].split(',')]
                with_files += 1
            if len(study["Excel Sheet"]) > 0:
                study_dict["excel"] = study["Excel Sheet"].strip()
            current_keys[study["Folder Name"]] = study_dict
            added += 1
        else:
            print(f"Tried to add {study['Folder Name']} but the required columns are not all filled out")

with open("remapkeys.json","w") as outfile:
    json.dump(current_keys,outfile)

print(f"Added {added} new study remappings. ({with_files}/{added} with specified files.)")
print(f"Current Total: {len(current_keys)}")

# found in env file
CLIENT_ID = os.getenv('CLIENT_ID')
CLIENT_SECRET = os.getenv('CLIENT_SECRET')
ACCESS_TOKEN = os.getenv('ACCESS_TOKEN') # needs to be regenerated hourly

oauth2 = OAuth2(CLIENT_ID, CLIENT_SECRET, access_token=ACCESS_TOKEN)
client = Client(oauth2)

items = client.folder("231736074179").get_items()
items = [x.name for x in items]

with open("remapkeys.json",mode = 'r') as file:
    data = json.load(file)

total_processed = 0
for study in data:
    if study in items:
        data[study]['processed'] = True
        total_processed += 1
    else:
        data[study]['processed'] = False
        continue

with open("remapkeys.json",mode = 'w') as file:
    json.dump(data,file)

print("Marked processed studies.")
print(f"Total processed: {total_processed}")