Skip to Content

Changes for page 2 Script

Last modified by Devin Chen on 2026/03/10 10:53
From version 10.2
edited by Devin Chen
on 2025/12/10 10:32
Change comment: Update document after refactoring.
To version 18.1
edited by Devin Chen
on 2026/03/05 15:57
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1921,3 +1921,135 @@
1921 1921  {{info}}
1922 1922  ✎Note: If you want to use CMD to access Mysql, you need to add the bin file to the environment variable, please check online for details of the operation.
1923 1923  {{/info}}
1924 +
1925 +== **2.8 Google sheet** ==
1926 +
1927 + In this demo you can upload the real-time tags data to google sheet
1928 +
1929 +**1.Google sheets setting**
1930 +
1931 +1.1 Create a table file based on actual needs and fill in the headers.
1932 +
1933 +(% style="text-align:center" %)
1934 +[[image:WeCom Screenshot_20260305114836.png||height="618" width="977"]]
1935 +
1936 +1.2 Modify the access permissions of the table to 'Anyone on the internet with the link can edit'.
1937 +
1938 +(% style="text-align:center" %)
1939 +[[image:z6rgXmeOMz1.png||height="621" width="982"]]
1940 +
1941 +1.3 Enable the Apps Script extension feature for the sheet.
1942 +
1943 +(% style="text-align:center" %)
1944 +[[image:u8QbgKcOgA.png||height="621" width="982"]]
1945 +
1946 +1.4 On the Apps Script editing page, program according to actual needs.
1947 +
1948 +Google Apps Script Demo
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 + }
1968 +
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 +
2052 +
2053 +{{/info}}
2054 +
2055 +
3W00uSwkVQ1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +140.1 KB
Content
8a8vG9Z7IT.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +132.2 KB
Content
Doa6Qhp4bS.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +139.2 KB
Content
HoLYu2nzvq.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +136.6 KB
Content
PixPin_2026-03-05_14-40-27.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +154.8 KB
Content
RW7OUCVVp1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +100.0 KB
Content
WeCom Screenshot_20260305114836.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +53.5 KB
Content
WeCom Screenshot_20260305153954.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +71.7 KB
Content
u8QbgKcOgA.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +61.3 KB
Content
z6rgXmeOMz.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +80.7 KB
Content
z6rgXmeOMz1.png
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.DevinChen
Size
... ... @@ -1,0 +1,1 @@
1 +80.7 KB
Content