Open the Google Sheet where your data is stored.

From the top menu, click:
Extensions → Apps Script.

This will open the Apps Script editor in a new tab.

Copy and paste the following script:

function doGet(e) {

  const sheetId = "{sheet_id}"; // <-- Replace with your real sheet ID 

  const sheetName = "Sheet1";

  const columnToSearch = 1; // e.g., Column B = 2

  const query = e.parameter.query || ""; // Example: ?query=Doe

  if (!query) {

      return ContentService

    .createTextOutput(JSON.stringify({ message: "Missing search query" }))

    .setMimeType(ContentService.MimeType.JSON);

  }

  const ss = SpreadsheetApp.openById(sheetId);

  const sheet = ss.getSheetByName(sheetName);

  const data = sheet.getDataRange().getValues();

  for (let i = 0; i < data.length; i++) {

    const cellValue = data[i][columnToSearch - 1].toString();

    if (cellValue.includes(query)) {

      const result = {

        row: i + 1,        // Optional: 1-based row number

        data: data[i]      // Entire matching row

      };

      return ContentService

        .createTextOutput(JSON.stringify(result))

        .setMimeType(ContentService.MimeType.JSON);

    }

  }

  return ContentService

    .createTextOutput(JSON.stringify({ message: "No matching row found." }))

    .setMimeType(ContentService.MimeType.JSON);

}

Copy the sheetId from your Google Sheet URL as shown.

Replace the sheetId from your Google Sheet URL.

Replace the name of the sheet as shown with the sheet name.

Replace the column where your unique identifier (e.g., email, ID) is stored. Use numbers:
For example, if you want to search by email stored in column B, set: const columnToSearch = 2;

Under Select type, choose Web app.

Fill out the details:

• Description: Enter something like Fetch data API.

• Execute as: Me (your account).

• Who has access: Anyone.
  
  
  

Click Deploy.

The first time you deploy, Google will ask for permissions. Click Authorize.

After deployment, Google will provide you with a Web App URL.

You can test the script in your browser with a value from your sheet’s search column.

It  will return a JSON response containing that row’s data.

Open your form builder in Zoho Forms. Go to Settings > Form Settings > Prefill > Dynamic Prefill Webhook.

Under Webhook Settings, select Employee ID as the Search Field. Make sure the field you select here matches the column you set as columnToSearch in your Google Script.

Under Webhook URL, paste the Web App URL you copied.

Enter the Record Identifier as query. Click Next

Test the webhook with search field value like 1002 and check if the received response is valid.

Map the fields from your sheet’s response to the corresponding form fields.

Click Save.