JSON-LD Bulk Editor and File Generator for Google Sheets • Trevor Fox

As Schema.org JSON-LD usage spreads across the internet and its utility increases, I find myself creating a lot more JSON-LD templates for non-standard entities… and then I find myself copying them over and over for each of their sibling entities. Finally, I decided I had had enough of this and looked to solve this problem for myself and other SEOs in this same boat.

The Schema.org JSON-LD Bulk Editor for Google Sheets is a Google Spreadsheet template and script that takes a “prototype” JSON-LD template and allows to create as many customized template copies of it as you need by filling in a spreadsheet. It then allows you to save each JSON-LD schema into its own JSON file to add to your site or app.

JSON-LD Bulk Generator Use Cases

I admit, I believe in the future of the semantic web and because of that, I think there will be a lot more use of JSON-LD. Developers will be able to create a lot of interesting applications on top of all that nice and clean data and we will hopefully benefit from this easy access to information.

Google and others are already consuming JSON-LD but, because their primary focus is things like Products, Recipes, and Locations, developers have created JSON-LD generators for those specific and rigid schemas.

This tool allows you to create and edit the schema that perfectly fits your use case.

Example Use Cases:

You need a lot of customized markup for non-standard entities like CreativeWorks or Intangibles subclasses
It’s not worth building a schema into your CMS just to markup a few similar but unique pages
You want to add JSON-LD to several similar but unique hard-coded HTML pages
You are using Google Tag Manager but don’t have access to the variables needed for the application/ld+json HTML tag.
Hosting a few .jsonld files is easier than modifying your database

If you are wondering about how to put JSON-LD onto the page with Google Tag Manager, learn more about embedding JSON-LD with Javascript.

How to Edit and Generate JSON-LD Files

TO BEGIN: MAKE A COPY OF THE SPREADSHEET

Then follow these three steps:

Step 1: Paste your “prototype” schema

First, create a “prototype” schema that will be copied with unique values. Use a code editor to avoid any JSON syntax errors.

Then paste your prototype schema into the green box on the “Start” tab.

The schema of the prototype will be identical to that of its copies. You can modify the schema in the next step but it is easiest to start with a perfect prototype.

Step 2: Create and Edit the Spreadsheet Template

In the JSON-LD Bulk Editor menu, click Create schema editor.

Behind the scenes, Google Apps Script is “flattening” the JSON object and making a header row of object keys on the Generator tab and filling the second row with the values from the prototype object.

The header row shows the JSON object keys in “dot-notation” this is similar to how Google Tag Manager, MongoDB, and Javascript, in general, allow you to access values within objects. So if the schema is:

{"aggregateRating": { 
  "@type": "AggregateRating",
  "bestRating": "100",
  "ratingCount": "24",
  "ratingValue": "87" }
}

The dot-notation for rating value would be aggregateRating.ratingValue. This works for arrays in the same way. The 0th element of an array would be someArray.0 and the 12th element of the array would be someArray.12.

Now simply fill in the rows values that correspond to the header for each entity.

Step 3: Generate some JSON-LD

Now that each entity has been filled out in the spreadsheet, its time to make some files! Just go to the JSON-LD Bulk Editor menu and click Save JSON files to Drive.

Behind the scenes, Google Apps Script builds the JSON object based on the values in the spreadsheet. The schema is then saved to a Google Drive folder called “JSON-LD Schema {{timestamp}}”. Each file is saved under the file name that was assigned on the spreadsheet with the file extension, .jsonld.

Your .jsonld files are ready!

Make Some Semantic Web!

I hope you will find this tool as useful as I have. For an easy way to use these JSON files, check out:

For an easy way to use these JSON files, check out: embedding JSON-LD with Javascript.

If this works for you or you see any improvements that could be made, it would be great to hear from you in the comments.