Automatic updating of website by linking spreadsheet and wix [with code].

At KnowThyself Co., Ltd., we specialize in business automation and digital transformation in Osaka and Tottori. Leave your work efficiency and automation needs to us.

Please feel free to contact us for solutions ranging from straightforward automation with GAS in Excel and spreadsheets to complex API integrations, RPA, and business applications. We also excel in automating CAD and design tasks, particularly in the manufacturing industry.

Our goal is not only to address labor shortages but also to empower businesses by reducing overtime and expanding opportunities for creative work.

The code for the previously introduced Crowd funding automation system is now available.

Updating a website is a hassle, isn't it? This time, a must-see for those who use wix to create their homepages. We have created and automated the code while also using chatgpt. It is surprisingly easy to do, so please give it a try.

Automation Flow

１．Prepare the spreadsheet

２．Integrate GAS into the spreadsheet

３．Create arbitrary HTML code in wix

４． Change arbitrary text ID

１．Preparation of Spreadsheets (Business Efficiency Procedure (1))

Automatic updating of website by linking spreadsheet and wix [with code].

First, prepare the sheet you wish to automate. The figure below is a sample of the sheet used in the previous article on the automation of Kurafan.

In this case, the information to be automatically updated by wix is "Current Amount": column C, "Achievement Rate": column D, and "Number of People Supported": column F.

As a sample, we will introduce the code to link "Current Amount": C2 cell, "Achievement Rate": D2 cell, and "Number of Supporters": F2 cell.

２．Incorporate GAS into the spreadsheet side (Business Efficiency Procedure #2)

Embedded GAS (appscript)

Open "appscript" from the spreadsheet sheet.

When "appscript" opens, a screen similar to the one below will appear.

function myFunction() { }

Erase the code shown above and enter the code below.

function doGet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName('Sheet name');

// Get the value of each cell and round it to an integer

var valueC2 = Math.round(sheet.getRange("C2").getValue());

var valueD2 = Math.round(sheet.getRange("D2").getValue());

var valueF2 = Math.round(sheet.getRange("F2").getValue());

// Stores values in an array

var values = [valueC2, valueD2, valueF2];

var output = ContentService.createTextOutput();

output.setMimeType(ContentService.MimeType.JSON);

output.setContent(JSON.stringify({

text1: values[0],

text2: values[1],

text3: values[2],

}));

return output;

}

This code corresponds to "Current Amount": cell C2, "Percent Achieved": cell D2, and "Number of People Supported": cell F2. Change the "Progress Check" in the sheet name section to the name of the sheet in your spreadsheet.

Also, the above code corresponds to one cell in each C2, D2, and F2, so if you want to apply it to multiple cells, please modify the code.

Deploying Scripts

Select "Deploy" and then "New Deploy" as shown below.

On the new Deploy screen, Description: Enter any content. Web app: "Run as user" is assumed to be you this time, but you can change it as you wish.

Accessible users: This time it is set to "All", but please change it to any state you wish. Please be careful to select this option for security purposes.

After the new deployment is complete, copy the Deploy ID. This ID will be used when entering the html code into wix, so please save it somewhere.

３．Create any HTML code in wix (Business efficiency procedure (3))

Enable development mode

As a preliminary step, activate the development mode. The figure below shows the enabled state.

Add HTML code within wix

From the menu on the left, add HTML parts by going to Add Parts, then HTML Code.

Ading HTML Code in Wix

Checking the ID of the HTML Code

When you select the added HTML code, a screen like the one shown below will appear.

At this time, because the development mode is enabled, it is possible to check the ID. The part framed in red in the below figure is the ID of this HTML code. In this article, we refer to this ID as 'html1'.

*Note: In this article, we are only using it to check the ID, so if the development mode screen is in the way, it is fine to disable development mode.

Embedding HTML Code

Select the added HTML code and click 'Enter Code'.

Add code internally as shown in the figure below.

"Please change the parts marked in red in the following code to arbitrary values:

• The 'Deployment ID' set in the spreadsheet's App Script

• The 'HTML ID' set in Wix

• The text number in GAS (App Script) (in this case, text1) *Please check the above GAS (App Script) for reference."

HTML Code

<div id="html ID：" s

tyle="font-size: 45px; font-family: 'Arial', sans-serif; color: black;"></div>

<script>

function fetchData() {

fetch('https://script.google.com/macros/s/Deployment ID: The code deployed and copied in Step 2/exec')

.then(response => response.json())

.then(data => {

document.querySelector('#htmlのID').innerHTML = data.Usage number of textc;

});

}

// Execute once when the site is loaded

fetchData();

// Thereafter, execute every 24 hours

setInterval(fetchData, 86400000);

</script>

Publishing in Wix

"Finally, press 'Publish' in Wix to complete the process. This links the spreadsheet with Wix, and the values inside the cells will automatically be displayed in the HTML section of Wix. Although not detailed in this article, in Crowdfunding automation, we have automated the updating of aggregate purchase information by integrating with an EC site and its API."

This translation captures the technical steps described in the original text, providing clear instructions for embedding and configuring HTML code in Wix, as well as linking it with a spreadsheet and GAS.