Theo's Typesafe Cult•12mo ago

Google sheet api / scaleable api

How can i make a api that can handle 100 post requests at a time. This is a expensive function that does a lot of querys to prisma db. It will also have to be rate limited. I would love to send 100 post requests and it tells the 4th request to wait 60 seconds, the 8th request 120 seconds, 12th request 150 seconds etc Does anyone have tips on google sheetapi, when i get row values, find row index that existing row is on, and update. sometimes a different edit happens on the google sheet and the row index is now incorrect updating the wrong row, losing values. How can i search a google sheet for a row with a phone number in a column if i find it update that row to new values, if i dont create a new row

23 Replies

Keef•12mo ago

So you are wrapping a sheet with some kind of real time collaborative editor in the form of an app or whatever? The rate limits they give seem find. Could you show some logic/code for what you are doing thats triggering the rate limit?

MattOP•12mo ago

i got my rate limits raised im still hitting it can i call you its honestly pretty fucking werid but i can explain it in 30 seconds. theres like 4 sheets that get looked up and i can make one function taht looks up all 4 instead of like 16

Keef•12mo ago

sorry i'm about to get to work. If you just provide more information, someone or me can get to it at our own pace which is the biggest thing about asking a question

Keef•12mo ago

https://srijancse.medium.com/how-real-time-collaborative-editing-work-operational-transformation-ac4902d75682

Medium

Building a real-time collaborative editor using Operational Transfo...

For this summers, I would have the incredible privilege of interning with Typeset. I am really excited for this opportunity and would be…

Keef•12mo ago

this might be a useful read for you but im not entirely convinced its relevant since I'm not sure yet what you are doing overall If you just have multiple users trying to find a row by a value that can change by the time you get to the update portion you will prob have to introduce some kind of locking mechanism to ensure 1 writer

MattOP•12mo ago

YES

Keef•12mo ago

you can do a distributed lock through redis to guarantee this stuff but this makes it more complex obv

MattOP•12mo ago

is that the best way to do it. i dont have to change the way im doing querys with google sheets i need to like gate the entire api handler or something? Is there a way to add into my query for google sheets to lock it until my next query. Im doing alot of this.

//usees reads / writes
  const rows = await getRowValues({
    sheets,
    sheetId: sheetId,
    range: "Sheet1",
  });
// js only
  const { foundRowIndex, foundRow } = findRow({
    fullName: mgtName,
    phone: null,
    rows,
  });

  if (
    foundRow &&
    typeof foundRow[3] === "string" &&
    typeof foundRow[6] === "string"
  ) {
    const sheetIdReturn: string = foundRow[3];
    const range = `${foundRow[6]}!Ax:Ox`;
    if (sheetIdReturn && range) {
// only updates row at sheet id and range. either uses foundrowIndex or rowvalues.length+1
      const a = await addUpdateLead({
        contact,
        sheets,
        sheetId: sheetIdReturn,
        range: range,
        values: values,
      });

//usees reads / writes
  const rows = await getRowValues({
    sheets,
    sheetId: sheetId,
    range: "Sheet1",
  });
// js only
  const { foundRowIndex, foundRow } = findRow({
    fullName: mgtName,
    phone: null,
    rows,
  });

  if (
    foundRow &&
    typeof foundRow[3] === "string" &&
    typeof foundRow[6] === "string"
  ) {
    const sheetIdReturn: string = foundRow[3];
    const range = `${foundRow[6]}!Ax:Ox`;
    if (sheetIdReturn && range) {
// only updates row at sheet id and range. either uses foundrowIndex or rowvalues.length+1
      const a = await addUpdateLead({
        contact,
        sheets,
        sheetId: sheetIdReturn,
        range: range,
        values: values,
      });

const res = await sheets.spreadsheets.values.get({ spreadsheetId: sheetId, range: range, }); like if this query could provide a code that locks all other querys unless you use that code so that my update goes through on the correct index

MattOP•12mo ago

https://srijancse.medium.com/how-real-time-collaborative-editing-work-operational-transformation-ac4902d75682 Bro what is this? This is a blanket theory that i can learn and use to lots of different things that cause problems like im running into. I need to code this into my nodejs serverless function?

Medium

Building a real-time collaborative editor using Operational Transfo...

For this summers, I would have the incredible privilege of interning with Typeset. I am really excited for this opportunity and would be…

Keef•12mo ago

Its a idea that enables you to do what sheets basically gives in the app The redis lock thing is a way to add locking to a system with minimum latency and works for serverless (but you need to start a redis server peepoAing

) Its unrelated to the redis lock

MattOP•12mo ago

i tihnk i have that const mutex = new Mutex(); const ratelimit = new Ratelimit({ redis: Redis.fromEnv(), limiter: Ratelimit.slidingWindow(5, "60 s"), analytics: true, }); const c = new Client({ token: process.env.QSTASH_TOKEN!, }); export const contactUpsertRouter = createTRPCRouter({ hello: publicProcedure .meta({ openapi: { method: "POST", path: "/lead-update" } }) .input(crmSchema) .output(z.object({ greeting: z.string() })) .query(async ({ input, ctx }) => { const delayMilliseconds = Math.floor(Math.random() * 5000); // Adjust the range as needed let contact = await parseContact(input, ctx.db); // i made it do this to in case payPeople needs more updated values ran into double payment once or twice try { await new Promise((resolve) => setTimeout(resolve, delayMilliseconds)); contact = await parseContact(input, ctx.db); } catch (error) { console.error(error); } if (contact) { const release = await mutex.acquire(); await payPeople({ leadRow: contact, db: ctx.db }); const { success } = await ratelimit.blockUntilReady("ok", 60_000); if (!success) { console.log("error"); } await googleSheetContact({ contact: contact }); release(); } return { greeting: Hello , }; }), please dont laugh too hard

Keef•12mo ago

most people won't critize you for your code unless you don't dump it in a code block next time triple tick language and paste it like such

MattOP•12mo ago

ahh nice so wtf is mutex even doing, why do i like it lol

Keef•12mo ago

add a ts next to the ticks

MattOP•12mo ago

how did u get coloring?

const mutex = new Mutex();

const ratelimit = new Ratelimit({
  redis: Redis.fromEnv(),
  limiter: Ratelimit.slidingWindow(5, "60 s"),
  analytics: true,
});
const c = new Client({
  token: process.env.QSTASH_TOKEN!,
});
export const contactUpsertRouter = createTRPCRouter({
  hello: publicProcedure
    .meta({ openapi: { method: "POST", path: "/lead-update" } })

    .input(crmSchema)
    .output(z.object({ greeting: z.string() }))
    .query(async ({ input, ctx }) => {
      const delayMilliseconds = Math.floor(Math.random() * 5000); // Adjust the range as needed

      let contact = await parseContact(input, ctx.db);

// i made it do this to in case payPeople needs more updated values ran into double payment once or twice
      try {
        await new Promise((resolve) => setTimeout(resolve, delayMilliseconds));
        contact = await parseContact(input, ctx.db);
      } catch (error) {
        console.error(error);
      }
      if (contact) {
        const release = await mutex.acquire();
        await payPeople({ leadRow: contact, db: ctx.db });

        const { success } = await ratelimit.blockUntilReady("ok", 60_000);

        if (!success) {
          console.log("error");
        }
        await googleSheetContact({ contact: contact });
        release();
      }

      return {
        greeting: Hello ,
      };
    }),

const mutex = new Mutex();

const ratelimit = new Ratelimit({
  redis: Redis.fromEnv(),
  limiter: Ratelimit.slidingWindow(5, "60 s"),
  analytics: true,
});
const c = new Client({
  token: process.env.QSTASH_TOKEN!,
});
export const contactUpsertRouter = createTRPCRouter({
  hello: publicProcedure
    .meta({ openapi: { method: "POST", path: "/lead-update" } })

    .input(crmSchema)
    .output(z.object({ greeting: z.string() }))
    .query(async ({ input, ctx }) => {
      const delayMilliseconds = Math.floor(Math.random() * 5000); // Adjust the range as needed

      let contact = await parseContact(input, ctx.db);

// i made it do this to in case payPeople needs more updated values ran into double payment once or twice
      try {
        await new Promise((resolve) => setTimeout(resolve, delayMilliseconds));
        contact = await parseContact(input, ctx.db);
      } catch (error) {
        console.error(error);
      }
      if (contact) {
        const release = await mutex.acquire();
        await payPeople({ leadRow: contact, db: ctx.db });

        const { success } = await ratelimit.blockUntilReady("ok", 60_000);

        if (!success) {
          console.log("error");
        }
        await googleSheetContact({ contact: contact });
        release();
      }

      return {
        greeting: Hello ,
      };
    }),

beuatiful

Keef•12mo ago

boom

MattOP•12mo ago

thank you

Keef•12mo ago

So which line in there is a writer

MattOP•12mo ago

paypeople probably gonna do like 40 prisma db updates and a few api post requests

Keef•12mo ago

and thats when you fall out of sync ? as in index lost

MattOP•12mo ago

nah that function hasnt given me issues in a month. once it double paid but i think that cheeky timeout might have avoided that

Keef•12mo ago

that cheeky timeout should prob be addressed at some point lol

MattOP•12mo ago

the googleSheetContact looks like this.

  const a = await updateLeadCeo({
        invoiceId: contact.mgt_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.mgt_name,
        amount: contact.mgt_amt,
      });
      array.push(a);
    }
    if (contact.fta_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.fta_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.fta_name,
        amount: contact.fta_amt,
      });
      array.push(a);
    }
    if (contact.div_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.div_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.div_name,
        amount: contact.div_amt,
      });
      array.push(a);
    }
    if (contact.fsl_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.fsl_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.fsl_name,
        amount: contact.fsl_amt,
      });
      array.push(a);
    }

  const a = await updateLeadCeo({
        invoiceId: contact.mgt_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.mgt_name,
        amount: contact.mgt_amt,
      });
      array.push(a);
    }
    if (contact.fta_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.fta_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.fta_name,
        amount: contact.fta_amt,
      });
      array.push(a);
    }
    if (contact.div_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.div_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.div_name,
        amount: contact.div_amt,
      });
      array.push(a);
    }
    if (contact.fsl_name) {
      const a = await updateLeadCeo({
        invoiceId: contact.fsl_inv,
        contact,
        sheets,
        sheetId,
        mgtName: contact.fsl_name,
        amount: contact.fsl_amt,
      });
      array.push(a);
    }

so each one of these looks at a master sheet with like 3000 rows, finds the ceo, goes to their sheet with possibly thousands of rows and grabs all values, using js to finds a matching row, then updates that range specifically. Each function is updating a different sheet though. so none of these functions can step on each other toes. The issue arises when i send say 20 post requests at once to this function. The rows update in all different order. ill watch 7 rows pop up, and ill see a bunch of names flicker over the same rows. the missing 13 how would you address it. pay people basically looks at a prisma row, sees if a inv column is null, if it is it pays it then fills that column. I have had 2 post requests come in at same time by one of the users on my crm and it sent 2 payments because of that slight second

Gaming

Programming

Google sheet api / scaleable api