Riddhesh Ganatra

Automation in google sheet using Apps Script

Automation in google sheet using Apps Script

We had a requirement to send an SMS to a patient when there is an entry for an appointment for them in google Sheets.

Sample data:

Link for sample sheet:

Google Sheets: Sign-in

Step 1: Add button in google sheet

Insert -> Drawing

Step 2: Create a custom function that can be triggered on click on the button

Extensions -> Apps Script

Step 3: Code to send SMS for appointments

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('appoinments');
ja
  // 2 dimentional array for sheets data
  var data = sheet.getDataRange().getValues();
  var timeZone = Session.getScriptTimeZone()


  for (var i = 1; i < data.length; i++) {

    // condition to send message to whome we have not already sent message and first column is not blank
    if (data[i][4] != "done" && data[i][0] != "") {
      var date = Utilities.formatDate(data[i][2], timeZone, 'dd-MM-yyyy')
      var time = Utilities.formatDate(data[i][3], timeZone, 'h:mm a')
      var name = data[i][0]
      var phonenumber = data[i][1]

      Logger.log("date: "+date)
      Logger.log("time: "+time)
      Logger.log("name: "+ name)
      Logger.log("phonenumber: "+phonenumber)

      // API to send SMS(Any provider)
      // Hit api to send sms
      // var url = `Put your sms provider url here`

      // var response = UrlFetchApp.fetch(url);
      // var json = response.getContentText();

      // set status to done, so we dont send messages again for same appoinments
      sheet.getRange(i + 1, 5).setValue('done')
    }
  }
}

Step: run and give permissions

Click on Advance on the bottom left :

Click on, go to the untitled project

Click on allow:

We can see logs as expected:

Now we can see the status column set to “done”

Step: Attach the function to button

Click on the button > assign a script > write the name of the function which we just made

Now add new appointments to the sheet and keep the appointment status blank. When we click on send message button, an SMS will be sent and the status will be updated in the sheet.

Share this with anybody you think would benefit from this. Have any suggestions or questions? Feel free to message me on LinkedIn.

We at Code B are a team of Fullstack software developers, passionate and dedicated to growing businesses for clients.

We have experience in Web Applications(Frontend and Backend), Mobile Applications(Native and Hybrid), DevOps (AWS, GCP, Azure, Digital Ocean, and Heroku), Blockchain(Solana), and UI/UX Design(Figma).

Contact us if you need any help with Software.

Mar 25, 2023

Riddhesh Ganatra

Tech for Empowering Chingari’s Video Content Creators with Blockchain

Chingari’s 50-member tech team formed over a couple of years built the first social media platform that rewards its Content Creators and Users for earning passive income through Crypto.

Gari service and Transaction Sync service are the primary services responsible for blockchain integration.

High-level Architecture for Blockchain integration:

Gari Service’s role is to integrate with blockchain for the following features:

• Create wallet and wallet management
• Support internal transactions(App to App)
• Support external transactions(Exchange to App and vice versa)
• Give Airdrops and Rewards

Transaction Sync service’s role is to sync Gari service with Blockchain.

High-level Tech stack:

• Node js (Nest js)
• AWS Aurora (Postgres)
• AWS SQS
• Kubernetes

Scale:

As of Feb 2023, below are the stats:

• Active wallets 24,13,500+
• 100k+ active daily miners
• number of transactions per month ~ 15,00,000

Different flows:

Our eventually consistent system is achieved using distributed transactions across multiple services using SQS and Sync service.

Create wallet flow:

“Create wallet” is initiated from Mobile applications by generating keypairs using Solana SDK. Gari service accepts these new keypairs, maps them to a particular user by storing the public key in Database, and sends transactions for creating a wallet to the blockchain.

TransactionSync service keeps checking the status of pending transactions and updates them to success or failure. 100 percent sync is guaranteed for all transactions by TransactionSync service.

Transaction Flow:

High-level frontend to backend flow for a transaction:

Frontend: Transaction object is made on the frontend(Mobile applications) using Solana SDK, partially signed by user’s private key(Only known to the frontend), encoded, and sent to gari service.

Backend: Decodes transaction back to transaction object, validates the transaction, partially signs with Chingaris main wallet(Since Chingari is the fee payer), and sends it to the blockchain. TransactionSync service keeps checking the status of pending transactions and updates them to success or failure.

Transaction Backend flow chart:

Flow Between different Services:

Airdrop flow:

Airdrops are given to users by different services of Chingari for various modules. The rewards list is synced by the Gari sync service to its Database and then processed in a scalable asynchronous manner.

Using distributed transactions across multiple services using SQS and Sync service, Chingari is able to support stable wallet management and transactions for its users!

Check here for more details on the Gari token.

---

Tech for Empowering Chingari’s Video Content Creators with Blockchain was originally published in chingari on Medium, where people are continuing the conversation by highlighting and responding to this story.

Mar 20, 2023

Riddhesh Ganatra

Atomicity and Transactions

Data corruption in case of system crashes, power failures, and other errors is a nightmare in real production systems with heavy traffic.

We will see how Atomicity and Transactions will solve this problem with the example below.

We will make a simple ticket booking system with a high chance of race conditions.

Requirements: Every day we have 2 tickets available and more than 2 users try to book tickets at the same time.

Version 1:

app.post('/v1/book-ticket', async (req, res) => {
    try {
        // count number of tickets already booked for that date
        let ticketCount = await client.db("irctc-demo")
            .collection("ticketsV1")
            .countDocuments({ date: req.body.date })


        if (ticketCount > 2) {
            return res.json({ message: `already full` })
        }


        // adding this line makes easy to test parallel requests
        await wait(10000)

        // book ticket for user
        const result = await client.db("irctc-demo")
            .collection("ticketsV1")
            .insertOne({ email: req.body.email, date: req.body.date });

        res.json({ id: result.insertedId })
    } catch (error) {
        console.log(error)
        res.json({ message: error.message })
    }
})

If we don't have this 10-second wait time, the difference between the count query in DB and the insert query in DB will be a few milliseconds and for testing, we will have to hit the API within milliseconds for which we have to use load testing tools like JMeter, artillery, etc

So a 10-second wait time is added in the above code to make parallel request testing easy.

Result:

When different users hit version 1 API within 10 seconds for the same date, more than 2 tickets get created in this case.

Version 2:

app.post('/v2/book-ticket', async (req, res) => {
    try {

        // decrement number of available tickets for a particular date with condition
        let ticketCount = await client.db("irctc-demo")
            .collection("ticketCountV2")
            .updateOne(
                { date: req.body.date, count: { $lt: 3 } },
                {
                    $inc: { count: 1 },
                },
            )

        if (ticketCount.modifiedCount == 0) {
            console.log(`request error`);
            return res.json({ message: 'false' })
        }

        // fake code to simulate random power failure
        let randomNumber = Math.random() * 2;
        if (randomNumber < 1) {
            throw new Error(`power failure`)
        }

        const result = await client.db("irctc-demo")
            .collection("ticketsV2")
            .insertOne({ email: req.body.email, date: req.body.date });
        return res.json({ id: result.insertedId, message: 'success' })


    } catch (error) {
        res.json({ message: error.message })
    }
})

In this method, we save the count of tickets for all dates in different collection ticketCountV2 and use the atomic operator $inc with the condition of $lt. This code will only book max 2 tickets for a particular date no matter how many users are trying to book simultaneously.

query 1: decrement available tickets for a day in ticketCountV2 collection

query 2: creating ticket in ticketsV2 collection

The problem with this is when our server restarts for some random reason like a power failure between 2 different queries, the system will allow the booking of fewer than 2 tickets which is also a problem.

Version 3:

app.post('/v3/book-ticket', async (req, res) => {
    const session = client.startSession();
    try {
        const transactionOptions = {
            readConcern: { level: 'snapshot' },
            writeConcern: { w: 'majority' },
            readPreference: 'primary'
        };

        session.startTransaction(transactionOptions);

        // decrement number of available tickets for a particular date with condition
        let ticketCount = await client.db("irctc-demo").collection("ticketCountV3")
            .updateOne(
                { date: req.body.date, count: { $lt: 3 } },
                {
                    $inc: { count: 1 },
                }, {
                session
            }
            )

        if (ticketCount.modifiedCount == 0) {
            return res.json({ message: 'false' })
        }

        // fake code to simulate random power failure
        let randomNumber = Math.random() * 2;
        if (randomNumber < 1) {
            throw new Error(`some error`)
        }

        const result = await client.db("irctc-demo")
            .collection("ticketsV3")
            .insertOne({ email: req.body.email, date: req.body.date }, { session });

        await session.commitTransaction();

        return res.json({ id: result.insertedId, message: 'success' })

    } catch (error) {
        console.log(error)
        await session.abortTransaction();
        res.json({ message: error.message })
    } finally {
        await session.endSession();
    }
})

Database transactions to rescue: If there is a power failure after the first database query, the database will roll back changes automatically since the transaction will not be committed.

Happy path:

• step 1: decrement available tickets for a day in the ticketCountV2 collection
• step 2: Create a ticket in the ticketsV2 collection
• step 3: commit the transaction

In case of power failure between 2 queries:

• step 1: decrement available tickets for a day in the ticketCountV2 collection
• step 2: Create a ticket in the ticketsV2 collection
• power failure
• DB will automatically rollback since there is no commit for the transaction

Source Code: https://github.com/riddheshganatra/Atomicity-and-Transactions

Share this with anybody you think would benefit from this. Have any suggestions or questions? Feel free to message me on LinkedIn.

We at Code B would be happy to help if you need help.

Feb 9, 2023

Riddhesh Ganatra

Node js Race Conditions

One of the basic concepts for node js developers is understanding race conditions. Let's make a simple API that registers users for a newsletter to understand race conditions. The only requirement is user should be able to register only once.

We will use Node js, Express js, and MongoDB for development.

Step 1: Install Mongo DB

We will use docker to start the local MongoDB instance.

docker run -p 27017:27017 --name newsletter -d mongo:latest

Note: Please map volume if you want to persist data.

Step 2: version 1 API that will return ‘success’ if it's a new email or error for already enrolled

function wait(timeout) {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      resolve()
    }, timeout);
  })
}

app.post('/v1/register', async (req, res) => {
  try {
    // find if user is already registered
    let user = await client.db("newsletter").collection("usersV1").findOne({email:req.body.email})

    if (user) {
      return res.json({ message: `user already registered` })
    }

    // adding this line makes easy to test parallel requests from same user
    await wait(10000)

    const result = await client.db("newsletter").collection("usersV1").insertOne({email:req.body.email});

    res.json({ message: `user registered successfully` })
  } catch (error) {
    console.log(error)
    res.json({ message: error.message })
  }
})

step 3: test version 1 API with curl

curl --location --request POST 'localhost:3000/v1/register' \
--header 'Content-Type: application/json' \
--data-raw '{
    "email":"test1@gmail.com"

}'

When we hit this request from multiple terminals at the same time, same user is registered twice.

step 4: version 2API that will return ‘success’ if it's a new email or ‘error: for already enrolled

app.post('/v2/register', async (req, res) => {
  try {
    const result = await client.db("newsletter").collection("usersV2").insertOne(req.body);
    res.json({ message: `user registered successfully` })
  } catch (error) {
    console.error(error.message)
    res.json({ message: error.message })
  }
})

step 5: create a unique index for email field when we connect to DB

client.connect().then(async () => {
  console.log(`connected to DB`);
  await client.db("newsletter").collection("usersV2").createIndex({ email: 1 }, { unique: true })
  app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
  })
})

step 6: test v2 API

In the case of V2, never duplicate users will be registered, even when we get more than 1 request from the same user at the same time. We can also validate this using artillery or JMeter.

Complete code:

const express = require('express')
var bodyParser = require('body-parser')
const { MongoClient } = require('mongodb');

// const uri = "mongodb+srv://<username>:<password>@<your-cluster-url>/test?retryWrites=true&w=majority";
const uri = "mongodb://localhost:27017/newsletter";

const client = new MongoClient(uri);

const app = express()
const port = 3000

app.use(bodyParser.json())

app.get('/', (req, res) => {
  res.send('Hello World to Race Conditions')
})


app.post('/v1/register', async (req, res) => {
  try {
    // find if user is already registered
    let user = await client.db("newsletter").collection("usersV1").findOne({email:req.body.email})

    if (user) {
      return res.json({ message: `user already registered` })
    }

    // adding this line makes easy to test parallel requests from same user
    await wait(10000)

    const result = await client.db("newsletter").collection("usersV1").insertOne({email:req.body.email});

    res.json({ message: `user registered successfully` })
  } catch (error) {
    console.log(error)
    res.json({ message: error.message })
  }
})

app.post('/v2/register', async (req, res) => {
  try {
    const result = await client.db("newsletter").collection("usersV2").insertOne({email:req.body.email});
    res.json({ message: `user registered successfully` })
  } catch (error) {
    console.error(error.message)
    res.json({ message: error.message })
  }
})



function wait(timeout) {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      resolve()
    }, timeout);
  })
}

client.connect().then(async () => {
  console.log(`connected to DB`);

  await client.db("newsletter").collection("usersV2").createIndex({ email: 1 }, { unique: true })

  app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
  })
})

GitHub repo:

GitHub - riddheshganatra/nodejs-race-conditions

Share this with anybody you think would benefit from this. Have any suggestions or questions? Feel free to message me on LinkedIn

Jan 4, 2023

Riddhesh Ganatra

Solana web3 js

Basic operations on Solana using web3 js.

Libraries:

var web3 = require('@solana/web3.js');
var splToken = require('@solana/spl-token');

Connection and generating keypairs:

const connection = new web3.Connection(
        web3.clusterApiUrl('testnet'),
        'confirmed',
      );

// generate keypair
    var fromWallet = web3.Keypair.generate();
    var toWallet = web3.Keypair.generate();

Get balance:

    console.log(await connection.getBalance(fromWallet.publicKey));
    console.log(await connection.getBalance(toWallet.publicKey));

// add some initial balance. Not possible in production.
    var fromAirdropSignature = await connection.requestAirdrop(
        fromWallet.publicKey,
        1000000000, // lambports
      );
    await connection.confirmTransaction(fromAirdropSignature);

console.log(await connection.getBalance(fromWallet.publicKey));

Transaction:

var transaction = new web3.Transaction().add(
        web3.SystemProgram.transfer({
          fromPubkey: fromWallet.publicKey,
          toPubkey: toWallet.publicKey,
          lamports: 10000,
        }),
      );

// Sign transaction, broadcast, and confirm
      var signature = await web3.sendAndConfirmTransaction(
        connection,
        transaction,
        [fromWallet],
      );
      console.log('SIGNATURE', signature);

console.log(await connection.getBalance(fromWallet.publicKey));
    console.log(await connection.getBalance(toWallet.publicKey));

Multi sign transaction(partialSign)

var feePayerWallet = web3.Keypair.generate();
    var feePayerAirdropSignature = await connection.requestAirdrop(
      feePayerWallet.publicKey,
      1000000000, // lambports
    );
    await connection.confirmTransaction(feePayerAirdropSignature);

var fundedTransaction = new web3.Transaction({
      feePayer: feePayerWallet.publicKey
    }).add(
      web3.SystemProgram.transfer({
        fromPubkey: fromWallet.publicKey,
        toPubkey: toWallet.publicKey,
        lamports: 10000,
      }),
    );

// partially sign transaction
    let blockhashObj = await connection.getRecentBlockhash();
    fundedTransaction.recentBlockhash = await blockhashObj.blockhash;

fundedTransaction.partialSign(fromWallet);

// encoding tranaction so that we can send it to backend
    let endocdeTransction = fundedTransaction.serialize({
      requireAllSignatures: false,
      verifySignatures: false,
    });

// can be transfered over API to backend if required
    endocdeTransction = endocdeTransction.toJSON()

let transactionFromJson = web3.Transaction.from(endcodedTransction);

transactionFromJson.partialSign(feePayerWallet);

const wireTransaction = transactionFromJson.serialize();
      const signature = await this.connection.sendRawTransaction(
        wireTransaction,
      );

Github repo: https://github.com/riddheshganatra/solana-tutorial

Have any suggestions? Feel free to message me on LinkedIn.

Oct 18, 2021