How to Generate LocalBusiness Schema in Google Sheets with Apps Script

How to Generate LocalBusiness Schema in Google Sheets with Apps Script

This Google Apps Script provides a simple yet powerful way to automate LocalBusiness schema generation for Google Business Profile listings. By implementing this in your workflow, you can enhance your SEO, improve search visibility, and streamline local business data management.

If you’re managing multiple Google Business Profile listings and need to generate structured data (JSON-LD) for the respective webpages, this Google Apps Script will automate the process directly in Google Sheets. This guide will walk you through how to implement the script, what it does, and how to use it.

What This Script Does

The script, named wozzylocal, extracts relevant business details from your Google Business Profile (GBP) export and formats them into LocalBusiness schema in JSON-LD format. The generated structured data can then be used to enhance your local SEO and improve search visibility.

Key Features:

• Works directly with Google Business Profile (formerly Google My Business) exports.
• Extracts essential business details such as name, address, phone number, website, and category.
• Formats the extracted data into LocalBusiness structured data.
• Adds opening hours dynamically based on availability.
• Includes social media links if available.
• Writes the final JSON-LD schema in a new column for each business listing.

Step-by-Step Guide to Implementing the Script

1. Export Your Google Business Profile Listings

Before running the script, ensure you have exported your Google Business Profile data into Google Sheets. The export should include the following columns:

• Business name
• Address details (Street, Locality, Region, Country, Postcode)
• Primary phone
• Website
• Primary category
• Opening hours (Sunday–Saturday hours)
• Social media links (Facebook, Instagram, LinkedIn, Twitter, YouTube)

2. Open the Google Apps Script Editor

1. Click on Extensions in the top menu.
2. Select Apps Script.
3. Delete any existing code and replace it with the script below.

The Apps Script Code

function wozzylocal() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];  // Extract column headers
  var outputColumn = headers.length + 1;  // New column for schema
  
  // Add column header
  sheet.getRange(1, outputColumn).setValue("LocalBusiness Schema");

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

    // Extract relevant fields
    var businessName = row[headers.indexOf("Business name")];
    var address = row[headers.indexOf("Address line 1")];
    var locality = row[headers.indexOf("Locality")];
    var region = row[headers.indexOf("Administrative area")];
    var country = row[headers.indexOf("Country/Region")];
    var postcode = row[headers.indexOf("Postcode")];
    var phone = row[headers.indexOf("Primary phone")];
    var website = row[headers.indexOf("Website")];
    var category = row[headers.indexOf("Primary category")];
    var lat = "";  // If you have latitude column
    var lng = "";  // If you have longitude column

    // Construct schema JSON
    var schema = {
      "@context": "https://schema.org",
      "@type": "LocalBusiness",
      "name": businessName,
      "address": {
        "@type": "PostalAddress",
        "streetAddress": address,
        "addressLocality": locality,
        "addressRegion": region,
        "postalCode": postcode,
        "addressCountry": country
      },
      "telephone": phone || "",
      "url": website || "",
      "priceRange": "$$",  // Modify if price information is available
      "openingHoursSpecification": [],
      "geo": {
        "@type": "GeoCoordinates",
        "latitude": lat,
        "longitude": lng
      },
      "sameAs": [] // Social media profiles
    };

    // Add social media links if available
    var socialPlatforms = [
      { key: "Place page URLs: Facebook", property: "url_facebook" },
      { key: "Place page URLs: Instagram", property: "url_instagram" },
      { key: "Place page URLs: LinkedIn", property: "url_linkedin" },
      { key: "Place page URLs: Twitter", property: "url_twitter" },
      { key: "Place page URLs: YouTube", property: "url_youtube" }
    ];

    socialPlatforms.forEach(platform => {
      var link = row[headers.indexOf(platform.key)];
      if (link) schema.sameAs.push(link);
    });

    // Add opening hours
    var days = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
    days.forEach(day => {
      var hours = row[headers.indexOf(day + " hours")];
      if (hours && hours !== "Closed") {
        schema.openingHoursSpecification.push({
          "@type": "OpeningHoursSpecification",
          "dayOfWeek": "https://schema.org/" + day,
          "opens": hours.split("–")[0]?.trim(),
          "closes": hours.split("–")[1]?.trim()
        });
      }
    });

    // Convert to JSON-LD format
    var schemaJson = JSON.stringify(schema, null, 2);

    // Write schema JSON to the new column
    sheet.getRange(i + 1, outputColumn).setValue(schemaJson);
  }
}

3. Run the Script

1. Click the Run button in the Apps Script editor.
2. Allow the necessary permissions when prompted.
3. The script will process each row and generate the LocalBusiness schema JSON-LD data in a new column.

4. Using the Output

Once the script completes, you’ll find a new column labeled LocalBusiness Schema in your sheet. Copy the JSON-LD output and add it to your website’s <script type="application/ld+json"> tag to enhance SEO.