Streamline Business Card Digitization with OCR and Google Apps Script: Save Time on Spreadsheet Data Entry

This function manages the entire process of extracting text from business card images stored in Google Drive, using the Google Vision API, and transferring the extracted data to a Google Spreadsheet.

function processBusinessCards() {
  const folderId = "Insert the ID of the Business Cards folder here";
  const folder = DriveApp.getFolderById(folderId);

• folderId: Specify the ID of the folder where the business card images are stored.
• You can retrieve the ID from the URL of the folder in Google Drive and input it here.
• folder = DriveApp.getFolderById(folderId): Use DriveApp to access the folder specified by the folderId.

  const processedFolder = folder.getFoldersByName("Processed").next(); // Folder for processed files

• processedFolder: Retrieves the folder named “Processed”.
• This folder is used to store business card files that have been processed.
• next(): Since getFoldersByName() returns an iterator, use next() to get the first folder from the list.

  const files = folder.getFiles();

• files = folder.getFiles(): Retrieves all the files contained in the specified folder.
• These files include the business card images to be processed.

  const visionApiKey = PropertiesService.getScriptProperties().getProperty("VISION_API_KEY");

• visionApiKey: Retrieves the Google Vision API key stored in the script properties. This key is required to make requests to the Google Vision API.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Insert sheet name here");

• sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(...): Retrieves the sheet with the specified name from the active Google Spreadsheet. This sheet is where the extracted data will be input. (e.g., BizCards)

  while (files.hasNext()) {
    const file = files.next();
    const imageBlob = file.getBlob();
    Logger.log("Processing file: " + file.getName());

• while (files.hasNext()): Continues looping as long as there are files remaining in the files object.
• file = files.next(): Retrieves the next file in the files object.
• imageBlob = file.getBlob(): Converts the file into a Blob format. The Blob format is used to handle binary data and is required for sending data to the API.

    const ocrText = callGoogleVisionApi(visionApiKey, imageBlob);

• ocrText = callGoogleVisionApi(...): Calls the callGoogleVisionApi function to extract text information from the image.

    if (ocrText) {
      Logger.log("OCR Text: " + ocrText);
      appendToSheet(sheet, ocrText);

      // Move processed files to "Processed" folder
      processedFolder.addFile(file);
      folder.removeFile(file);
      Logger.log("File moved to processed folder: " + file.getName());

• if (ocrText): Executes the following steps only if the OCR process is successful and text is detected.
• appendToSheet(sheet, ocrText): Adds the extracted text to the specified sheet.
• processedFolder.addFile(file) and folder.removeFile(file): Moves the processed file to the “Processed” folder by adding it to the folder and then removing it from the original folder.

This function handles the process of extracting text from images using the Google Vision API.

function callGoogleVisionApi(apiKey, imageBlob) {
  const url = `https://vision.googleapis.com/v1/images:annotate?key=${apiKey}`;

• url: The endpoint URL for the Google Vision API.
  The API key is included as a query parameter in the URL to authenticate the request.

  const requestBody = {
    "requests": [
      {
        "image": {
          "content": Utilities.base64Encode(imageBlob.getBytes())
        },
        "features": [
          {
            "type": "TEXT_DETECTION"
          }
        ]
      }
    ]
  };

• requestBody: The request body sent to the Vision API.
• "image": Contains the imageBlob encoded in Base64 format. This conversion ensures the image data is in a format that the API can process.
• "features": Specifies the feature to detect from the image.
  In this case, "TEXT_DETECTION" is used to extract text from the image.

  const options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(requestBody)
  };

• options: The configuration settings for the request sent to the Vision API via the UrlFetchApp.fetch() method.
• "method": Specifies the HTTP method to use. In this case, it is set to POST.
• "contentType": Defines the content type of the request. The value is set to application/json, as the request body is in JSON format.
• "payload": Converts the request body into a JSON string to be sent with the POST request.

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText());
    Logger.log("Vision API response: " + response.getContentText());
    const textAnnotations = json.responses[0].textAnnotations;
    if (textAnnotations && textAnnotations.length > 0) {
      return textAnnotations[0].description;
    }
  } catch (e) {
    Logger.log("Error during Vision API call: " + e.message);
  }
  return null;
}

• UrlFetchApp.fetch(url, options):
  Sends the request to the Google Vision API using the provided URL and options. The method retrieves the response from the API.
• JSON.parse(response.getContentText()):
  Converts the API response, which is returned as a string, into a JSON object for easier data manipulation.
• textAnnotations[0].description:
  Extracts the first entry from the textAnnotations array in the response, which contains the recognized text. This text is returned as the result of the OCR process.
• Error Handling with catch:
  If an error occurs during the API request or response parsing, the catch block logs the error message to the script logs, helping diagnose issues.