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
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?
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
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
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…
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
YES
you can do a distributed lock through redis to guarantee this stuff but this makes it more complex obv
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.
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
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…
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 
)
Its unrelated to the redis lock
)
Its unrelated to the redis locki 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 hardmost 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
ahh nice
so wtf is mutex even doing, why do i like it lol
add a ts next to the ticks
how did u get coloring?
beuatiful
boom
thank you
So which line in there is a writer
paypeople probably gonna do like 40 prisma db updates and a few api post requests
and thats when you fall out of sync ?
as in index lost
nah that function hasnt given me issues in a month. once it double paid but i think that cheeky timeout might have avoided that
that cheeky timeout should prob be addressed at some point lol
the googleSheetContact looks like this.
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