google_sheets_to_postgres.ts

/// <reference types="google-spreadsheet" />

import { GoogleSpreadsheet } from "google-spreadsheet";
import { Tool } from "@prisma/client";
import { prisma } from "../../app/clients/prisma";
import slugify from 'slugify';

require("dotenv").config();

async function main() {
  // Autenticação do Google Sheets API
  const doc = new GoogleSpreadsheet("");
  await doc.useServiceAccountAuth({
    client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL || "",
    private_key: process.env.GOOGLE_PRIVATE_KEY || "".replace(/\\n/g, "\n"),
  });

  // Carrega as informações da planilha
  await doc.loadInfo();

  // Seleciona a primeira guia da planilha
  const sheet = doc.sheetsByTitle['tools'];

  sheet.getRows({limit: 10, offset: 10}).then(rows => {
    rows.map(async row => {

      if (!!row.name && !!row.tags && !!row.link) {

        let linkObject = new URL(row.link);
        let recordBuild: Pick<Tool, 'name' | 'link' | 'summary' | 'description' | 'youtube' | 'price' | 'active' |  'slug'> = {
          name: row.name,
          link: linkObject.origin,
          summary: row.summary,
          description: row.description.replace(/(<([^>]+)>)/gi, ""),  
          youtube: row.youtube,
          price: row.price,         
          active: (row.active.toLowerCase() === "true") ? true : false,
          slug: createSlug(row.name)
        }
        
        let toolTeste = await prisma.tool.upsert({
          create: recordBuild,
          update: recordBuild,
          where: { slug: recordBuild.slug }          
        });

        await prisma.tool.update({
          data: { tags: { deleteMany: {} } },
          where: { id: toolTeste.id }
        })
        
        const tagsArray = row.tags.split(',');
        const tagSlugs = tagsArray.map((tag: string) => createSlug(tag));
        const existingTags = await prisma.tag.findMany({
          select: { id: true },
          where: { slug: { in: tagSlugs } }
        });
        
        await prisma.tool.update({
          where: { id: toolTeste.id },
          data: {
            tags: {
              create: existingTags.map(({ id }) => ({
                tag: {
                  connect: {
                    id: id
                  }
                }
              }))
            }
          },
        });
                
        if (recordBuild.slug != row.slug) {
          row.slug = recordBuild.slug;
          row.description = recordBuild.description;
          row.save();   

          console.log(`Tool ${recordBuild.name} atualizada com sucesso!`);
        }
                
      }
    });
  })

  console.log('Tools atualizadas com sucesso!');
}

function createSlug(input: string): string {
  return slugify(input, {
    replacement: '-', 
    lower: true,      
    strict: true,    
    trim: true     
  });
}

main()
  .catch((e) => {
    console.error(e);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });