Skip to Content

Changes for page 2 Script

Last modified by Devin Chen on 2026/03/10 10:53
From version 15.1
edited by Devin Chen
on 2026/03/05 15:06
Change comment: There is no comment for this version
To version 19.1
edited by Devin Chen
on 2026/03/05 16:05
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1931,21 +1931,206 @@
1931 1931  1.1 Create a table file based on actual needs and fill in the headers.
1932 1932  
1933 1933  (% style="text-align:center" %)
1934 -[[image:WeCom Screenshot_20260305114836.png]]
1934 +[[image:WeCom Screenshot_20260305114836.png||height="618" width="977"]]
1935 1935  
1936 1936  1.2 Modify the access permissions of the table to 'Anyone on the internet with the link can edit'.
1937 1937  
1938 1938  (% style="text-align:center" %)
1939 -[[image:z6rgXmeOMz1.png]]
1939 +[[image:z6rgXmeOMz1.png||height="621" width="982"]]
1940 1940  
1941 1941  1.3 Enable the Apps Script extension feature for the sheet.
1942 1942  
1943 1943  (% style="text-align:center" %)
1944 -[[image:u8QbgKcOgA.png]]
1944 +[[image:u8QbgKcOgA.png||height="621" width="982"]]
1945 1945  
1946 1946  1.4 On the Apps Script editing page, program according to actual needs.
1947 1947  
1948 1948  Google Apps Script Demo
1949 1949  
1950 +{{code language="JavaScript"}}
1951 +/**
1952 + * Handles POST requests: parses JSON data, writes it to the sheet with timestamp
1953 + * Expected JSON format: {
1954 + * sheetName: "Sheet1" (optional, defaults to "Sheet1"),
1955 + * integerNumber: 123,
1956 + * floatingNumber: 45.67,
1957 + * stringData: "example text"
1958 + * }
1959 + */
1960 +function doPost(e) {
1961 + // 1. Parse the JSON data from the request body
1962 + var data;
1963 + try {
1964 + data = JSON.parse(e.postData.contents);
1965 + } catch (error) {
1966 + return ContentService.createTextOutput('Error: Invalid JSON data');
1967 + }
1950 1950  
1951 -
1969 + // 2. Get the target sheet (defaults to "Sheet1")
1970 + var sheetName = data.sheetName || 'Sheet1';
1971 + var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
1972 + if (!sheet) {
1973 + return ContentService.createTextOutput('Error: Specified sheet not found');
1974 + }
1975 +
1976 + // 3. Verify required fields are present
1977 + if (data.integerNumber === undefined ||
1978 + data.floatingNumber === undefined ||
1979 + data.stringData === undefined) {
1980 + return ContentService.createTextOutput('Error: Missing required fields (integerNumber, floatingNumber, stringData)');
1981 + }
1982 +
1983 + // 4. Generate formatted current timestamp (first column)
1984 + var now = new Date();
1985 + var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
1986 + var formattedTimestamp = Utilities.formatDate(now, timeZone, "yyyy-MM-dd HH:mm:ss");
1987 +
1988 + // 5. Extract data from JSON
1989 + var integerNumber = data.integerNumber;
1990 + var floatingNumber = data.floatingNumber;
1991 + var stringData = data.stringData;
1992 +
1993 + // 6. Append the data to the sheet with proper column mapping:
1994 + // Column A: Time (timestamp)
1995 + // Column B: Integer number
1996 + // Column C: Floating number
1997 + // Column D: String data
1998 + sheet.appendRow([formattedTimestamp, integerNumber, floatingNumber, stringData]);
1999 +
2000 + // 7. Return success response
2001 + return ContentService.createTextOutput('Success: Data written to Google Sheets');
2002 +}
2003 +{{/code}}
2004 +
2005 +(% style="text-align:center" %)
2006 +[[image:PixPin_2026-03-05_14-40-27.png||height="623" width="985"]]
2007 +
2008 +1.5 Click "Deploy" to make a new deployment. Select the 'Web APP' type, set the user permissions, and complete the deployment of the script.
2009 +
2010 +(% style="text-align:center" %)
2011 +[[image:3W00uSwkVQ1.png||height="641" width="981"]]
2012 +
2013 +1.6 In the 'Manage deployments' interface, obtain the URL of the web app.
2014 +
2015 +(% style="text-align:center" %)
2016 +[[image:HoLYu2nzvq.png||height="631" width="966"]]
2017 +
2018 +2. Test the Web APP URL
2019 +
2020 +With the Web APP URL, we can test it via API testing tool (Postman, Hoppscotch)
2021 +
2022 +Web APP Handles POST requests: parses JSON data, writes it to the sheet with timestamp
2023 +
2024 +Header: [Content-Type: application/json]
2025 +
2026 +Expected JSON format: {
2027 +
2028 + sheetName: "Sheet1" (optional, defaults to "Sheet1"),
2029 +
2030 + integerNumber: 123,
2031 +
2032 + floatingNumber: 45.67,
2033 +
2034 + stringData: "example text"
2035 +
2036 + }
2037 +
2038 +(% style="text-align:center" %)
2039 +[[image:8a8vG9Z7IT.png||height="597" width="913"]]
2040 +
2041 +POST request execution effect
2042 +
2043 +(% style="text-align:center" %)
2044 +[[image:WeCom Screenshot_20260305153954.png||height="602" width="921"]]
2045 +
2046 +{{info}}
2047 +After successfully testing with the API tool, it is recommended to change the General access of the Google Sheet to 'Restricted.' This will help improve data security without affecting the use of the Web App.
2048 +
2049 +(% style="text-align:center" %)
2050 +[[image:RW7OUCVVp1.png||height="617" width="943"]]
2051 +{{/info}}
2052 +
2053 +**3. V-box settings**
2054 +
2055 +3.1 Real-time tags setting
2056 +
2057 +(% style="text-align:center" %)
2058 +[[image:PixPin_2026-03-05_16-00-15.png||height="291" width="982"]]
2059 +
2060 +3.2 Lua Script:
2061 +
2062 +3.2.1 Basic Configuration Information
2063 +
2064 +(% style="text-align:center" %)
2065 +[[image:PixPin_2026-03-05_16-02-35.png||height="451" width="480"]]
2066 +
2067 +3.2.2 Lua Script Demo
2068 +
2069 +{{code language="Lua"}}
2070 +-- Google Apps Script deployment URL (required)
2071 +local req_url = "https://script.google.com/macros/s/AKfycbxbAxKSysisJKdXeL5k1IuH4mYhnN2qOuq9ZbTtYJRQMSBgYi67eaqZRrS4JmhsA2dL/exec"
2072 +
2073 +function sheet.main()
2074 + -- Entry point: trigger the data sending process
2075 + prepareAndSendSheetData()
2076 +end
2077 +
2078 +-- Prepares data from PLC addresses and sends it to Google Sheets
2079 +-- Fetches: name (string) and score (word) from specified memory addresses
2080 +-- Constructs JSON payload and triggers HTTP POST request
2081 +
2082 +function prepareAndSendSheetData()
2083 +
2084 + float_num = addr_getfloat("@floating number")
2085 + -- Build the data payload to be sent to Google Sheets
2086 + local SheetData = {
2087 + sheetName = "Sheet1", -- Target sheet name
2088 + stringData = addr_getstring("@string", 20), -- Fetch string from address (max 20 chars)
2089 + integerNumber = addr_getword("@word"), -- Fetch 16-bit integer from address
2090 + floatingNumber = string.format("%0.4f", float_num) -- Fetch 32-bit floating number from address
2091 + }
2092 +
2093 + -- Directly use the single dataset as the message
2094 + local message = SheetData
2095 +
2096 + -- Debug output (optional, can be removed in production)
2097 + print("URL: " .. tostring(req_url))
2098 + print("Request Body: " .. json.encode(message))
2099 +
2100 + -- Send the request via HTTP POST
2101 + executeHttpPostRequest(req_url, message)
2102 +end
2103 +
2104 +
2105 +-- Executes HTTP POST request to Google Apps Script endpoint
2106 +-- @param req_url: target URL for the POST request
2107 +-- @param message: Lua table containing data to be sent (will be JSON-encoded)
2108 +
2109 +
2110 +function executeHttpPostRequest(req_url, message)
2111 + -- Required libraries
2112 + local json = require("json")
2113 + local ltn12 = require("ltn12")
2114 + local https = require("https")
2115 +
2116 + -- Encode the message table into a JSON string
2117 + local request_body = json.encode(message)
2118 + local max_redirects = 0 -- Number of redirects to follow (set to 0 for none)
2119 + local current_url = req_url
2120 +
2121 + -- Loop to handle potential redirects (currently limited to 0 redirects)
2122 + -- for redirect_count = 0, max_redirects do
2123 + local response_body = {}
2124 + local res, code, response_headers = https.request{
2125 + url = current_url,
2126 + method = "POST",
2127 + headers = {
2128 + ["Content-Type"] = "application/json", -- Must be text/plain to bypass CORS preflight
2129 + ["Content-Length"] = #request_body
2130 + },
2131 + source = ltn12.source.string(request_body),
2132 + sink = ltn12.sink.table(response_body)
2133 + }
2134 +
2135 +end
2136 +{{/code}}
8a8vG9Z7IT.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +132.2 KB
Content
PixPin_2026-03-05_16-00-15.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +187.2 KB
Content
PixPin_2026-03-05_16-02-35.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +60.0 KB
Content
RW7OUCVVp1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +100.0 KB
Content
WeCom Screenshot_20260305153954.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +71.7 KB
Content