Build website content from googlesheet (Zapier)
Updated: Jul 28, 2022
Objective:
Allow clients to update and add website contents via google sheet
Technology:
Google Sheet,
Wix Velo (repeater, collection, http-functions.js)
Zapier (3rd Party Integration Tool), webhooks
Limitation:
Cannot delete data as no real-time event handler is available on ZAPIER.
DemoLinks:
DEMO Video
STEP 1: Create google sheet via your Gmail account
Key Setup: Grant public readonly permissions
STEP 2 Create Collection via content manager
Key Setup: Column name of the Collection is the same as of google sheet column name
Key Setup: Set Collection permissions to: Anyone
STEP 3 : Create Repeater and connect it to dataset
STEP 4 : Define put_ method in http-functions.js
put_updateCreateRec method is defined to Update and Create a record, if not found.
post_insertProducts method is described to create a record.
Key Setup: Put method Calls Update only when _id is passed, which is Wix generated id
//Webhook url to be called by Zapier ZAP
//LIVE SITE URL https://www.ditaps.com/_functions/insertProducts
//https://snehatanushah.wixsite.com/contactmgmt/_functions-dev/insertProducts
export function post_insertProducts(request) {
let options = {
"headers": {
"Content-Type": "application/json"
}
};
// get the request body
return request.body.json()
.then((body) => {
// insert the item in a collection
// return wixData.insert("contacts", body);
return wixData.insert("productInfo", body);
})
.then((results) => {
options.body = {
"Product inserted": results
};
return created(options);
})
// something went wrong
.catch((error) => {
options.body = {
"error": error
};
return serverError(options);
});
}
//Webhook url to be called by Zapier ZAP
//URL FOR DEV ENV ONLY>>
//https://snehatanushah.wixsite.com/contactmgmt/_functions-dev/updateCreateRec
export function put_updateCreateRec(request) {
var execStatus;
var statusCode;
let options = {
"headers": {
"Content-Type": "application/json"
}
};
return request.body.text()
.then((body) => {
// update the item in a collection
const obj = JSON.parse(body);
console.log("code data fetched" + obj.code);
// console.log("name data fetched" + obj.name);
// console.log("price data fetched" + obj.price);
// console.log("description data fetched" + obj.description);
// console.log("buttonUrl data fetched" + obj.buttonUrl);
// console.log("imageUrl data fetched" + obj.imageUrl);
return wixData.query("productInfo")
.eq("code", obj.code)
.find()
.then((results) => {
if (results.items.length > 0) {
let item = results.items[0];
console.log("item found" + item.name);
item.name = obj.name;
item.price = obj.price;
item.description = obj.description;
item.buttonUrl = obj.buttonUrl;
item.imageUrl = obj.imageUrl;
item.details = obj.details;
execStatus = "Record Updated, 200 (OK) status code";
statusCode = 200;
//added now
options.body = {
"items": results.items
};
return wixData.update("productInfo", item)
//added now
.then((results) => {
options.body = {
"items": results.items
};
return ok(options);
})
// something went wrong
.catch((error) => {
options.body = {
"error": error
};
return serverError(options);
});
} else {
execStatus = "Record Inserted, 201 (Created) status code";
statusCode = 201;
return wixData.insert("productInfo", JSON.parse(body))
//added now
.then((results) => {
options.body = {
"items": results.items
};
// return ok(options);
return created(options);
})
// something went wrong
.catch((error) => {
options.body = {
"error": error
};
return serverError(options);
});
//added now
}
})
.catch((err) => {
let errorMsg = err;
//added now
options.body = {
"error": err
};
return serverError(options);
//added now
});
})
.then((results) => {
options.body = {
"codeExecuted": execStatus
};
if (statusCode === 201)
return created(options); //for inser
if (statusCode === 200)
return ok(options); //for update
})
// something went wrong
.catch((error) => {
options.body = {
"error": error
};
return serverError(options);
});
}Test Put Method Via Developer's Console
testcase1: Submit modified data with existing code:10005. Status return 200
testcase2: Submit new data with new code:10009. Status return 201
STEP 5 Create ZAP on ZAPIER (note your webhook)
Key Setup: Your endpoint URL to call the Wix webhook will differ based on Wix site implementation.
In my case, I'm using Wix Test - free site
https://snehatanushah.wixsite.com/contactmgmt/_functions-dev/updateCreateRec
https://www.ditaps.com/_functions/insertProducts
eg:
Fellow full stack developers, kindly leave a comment with the final project website name and how this was helpful to you in upgrading your client's site with this custom code patch. This will surely encourage DiTAPS to build more such blogs.