Build website content from googlesheet (Zapier)
Updated: Oct 6
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 as how this was helpful. This will surely encourage DiTAPS to build more such blogs.