Export template from Google Sheets to JSON - Waiviogit/waivio GitHub Wiki
- Create or open table in Google Sheets https://drive.google.com/
- Click on Extensions tab in top menu
- Click on Apps Script
- Add name (for example, Export JSON)
- Delete existing code
- Copy script code from here
- Insert code in script editor
- Click on Save project button
-
Come back to your table and refresh page
-
Now, you should have new tab in top menu.
if the new tab does not appear. Refresh page one more time or you can try to return to the script, add some comment(// comment), save again, return to the table and refresh page. Usually, when the page loads, the new tab appears later.
-
Open google template with fields and copy to your table.
-
You need to freeze the first row (View -> Freeze -> 1 row). This is important as this is the name of the fields to be exported.
1st time after clicking on "Export JSON for this sheet" you need to give permission to the script.
- Click Export JSON
- Click Export JSON for this sheet
- You will see Authorization Required modal window
- Click Continue
- Complete authorization and give permission
- Click Export JSON one more time
- You should see notification Running script and modal window with json
"Exported JSON" modal window has:
- direct link to download the file, leads to your google drive;
- json - that was created by script.
Every time when script is running, file is created on your Google Drive. Even if you edit the same table, every time file will be created. So, don't forget to delete unnecessary files from your Google Drive that were created in this way.
1st line of the table - these are the names of the fields, each next is a separate object:
2nd - object #1
3rd - object #2
4th - object #3
etc.
Below, table of all fields from the template.
Quick summary: Pay attention to fields:
-
categories
,imageURLs
- because different items should be separated by ";". -
features
- format for recording:key: Feature Name; value: Feature description*
, key from value must be separated by ";", each feature must be separated by "*". -
waivio_tags
- format for recording:key: Tag category; value: Tag*
, key from value must be separated by ";", each line must be separated by "*".
Field Name | Example Value | Description |
---|---|---|
asins |
The ASIN (Amazon identifier) used for this product. Field type: string |
|
brand |
The brand name of this product. Field type: string |
|
brandLink |
The permlink to existing business object. Field type: string Permlink is a unique object identifier. Can be seen in the link. |
|
categories |
A list of departments where the product can be found. Each department must be separated by ";". There is no need to put a ";" after the last department. Field type: array In order to successfully import objects such as a book and a restaurant, the relevant department must be in the list of categories (Books, Restaurant). |
|
colors |
A list of colors available for this product. That will be options with "Color" category name. Only one color option can be added. Field type: array |
|
dateAdded |
The date this product was first added. Field type: date |
|
dateUpdated |
The most recent date this product was updated. Field type: date |
|
descriptions |
The description for this product. Field type: nested |
|
dimension |
The length, width, and height of this product. Units included. Field type: string |
|
dontFetchAmazonOptions |
If you fill in the value of this field as true , then the options will not be pulled from Amazon but will be taken from the table. In the file it will look like:
|
|
features |
A list of features associated with this product. The feature list can be collected from specific feature sections available on product listings for the product. Format for recording features: key: Feature Name; value: Feature description* Key from value must be separated by ";". Each feature must be separated by "*". Key associated with fields:
Other keys will be written in features (list of characteristics). Field type: nested |
|
groupId |
The Group ID used for this product. Field type: string |
|
imageURLs |
A list of image URLs for this product. Each url must be separated by ";". There is no need to put a ";" after the last url. Field type: array |
|
isbn |
The ISBN code for this product. Field type: string |
|
manufacturer |
The manufacturer of this product. Field type: string |
|
manufacturerLink |
The permlink to existing business object. Field type: string Permlink is a unique object identifier. Can be seen in the link. |
|
merchants |
The merchant name of this product. Field type: nested |
|
merchantLink |
The permlink to existing business object. Field type: string Permlink is a unique object identifier. Can be seen in the link. |
|
mostRecentPriceAmount |
The product's most recent price amount scraped. Field type: float |
|
mostRecentPriceCurrency |
The currency listed for mostRecentPriceAmount. Field type: string |
|
name |
The product's name. Field type: string |
|
primaryImageURLs |
The product's avatar. Field type: array |
|
sizes |
A list of sizes available for this product. That will be option with "Size" category name. Only one size option can be added. Field type: array |
|
waivio_options |
A list of options with all fields for options for the product. Format for recording options: category: Options category; value: Value; position: 1; image: "https://imagelink"* Every field must be separated by ";". Each option must be separated by "*". One option has 4 fields: category and value are required. Field type: nested |
|
waivio_product_ids |
A list of product IDs with value associated with this product. Format for recording tags: key: Product ID type; value: Value* Key from value must be separated by ";". Each product ID must be separated by "*". Value can have only one option. Field type: nested |
|
waivio_tags |
A list of tags with tag categories associated with this product. Format for recording tags: key: Category Tag Name; value: Tag* Key from value must be separated by ";". Each tag must be separated by "*". Value can have only one option. Therefore, in order to add several tags to one category tag, they need to be recorded separately. Field type: nested |
|
weight |
The weight of the product. Units included. Field type: string |
- Use Google account when you don't have important files - because, every time when script is running, a file is created on your Google Drive. So from time to time you will need to clear the space. (Script running after you click "Export JSON for this sheet").
- In order to successfully import objects such as a book and a restaurant, the relevant department must be in the list of
categories
(Books, Restaurant). - Do not use ";" as a value in the field, if its
categories
,colors
,features
,sizes
,waivio_tags
because it is a separator for these fields. Use only as separator. - Do not use "*" as a value in the field, if its
features
,waivio_tags
because it is a separator for these fields. Use only as separator. - Don't change field names in first row, because there are correct field names.
- Don't change field order and don't delete columns, because the script is guided by the location index.
- It is not necessary to fill in all cells. Empty cells will be skipped.