Google Sheets

1
2/**
3* @OnlyCurrentDoc
4*/
5
6/**
7* Add a custom menu to show the QR dialog.
8*/
9function onOpen(e) {
10 SpreadsheetApp.getUi()
11   .createMenu('Custom Menu')
12   .addItem('Generate QR (Dialog)', 'showQRDialog')
13   .addToUi();
14}
15
16/**
17* Show the QR code dialog by rendering `QRDialog.html` (which includes JS/CSS).
18*/
19function showQRDialog() {
20 var template = HtmlService.createTemplateFromFile('QRDialog');
21 var htmlOutput = template.evaluate()
22     .setWidth(400)
23     .setHeight(400);
24 SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'QR Generator');
25}
26
27/**
28* Returns the value of the active cell in the current sheet.
29*/
30function getActiveCell() {
31 var value = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
32 // Return null or empty if there is no value
33 return value ? String(value) : null;
34}
35
36/**
37* Helper function for using `<?!= include('filename') ?>` in templated HTML.
38*/
39function include(filename) {
40 return HtmlService.createTemplateFromFile(filename).getRawContent();
41}
42
43
44

1/***********************************
2 * Replace with your own configuration
3 ***********************************/
4var SPREADSHEET_ID = "your-id";
5var SHEET_NAME = "your-sheet-name";
6
7/**
8 * doGet(e)
9 * This function is triggered when a GET request is sent to the web app URL.
10 * In this example, we:
11 *   1) Read the latest rows from the sheet
12 *   2) Return them as a JSON response
13 */
14function doGet(e) {
15  try {
16    // Open spreadsheet and select sheet
17    var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
18    var sheet = ss.getSheetByName(SHEET_NAME);
19    
20    // Get data from the sheet
21    var data = sheet.getDataRange().getValues();
22    
23    // Convert the sheet data into JSON
24    // The first row is assumed to be the header.
25    var headers = data[0];
26    var rows = data.slice(1); // all except header
27    
28    // Only get the last 10 rows (adjust as needed)
29    var recentRows = rows.slice(-10);
30    
31    // Build array of objects for JSON
32    var output = recentRows.map(function(row) {
33      var obj = {};
34      headers.forEach(function(h, i) {
35        obj[h] = row[i];
36      });
37      return obj;
38    });
39    
40    // Return JSON
41    return ContentService
42      .createTextOutput(JSON.stringify(output))
43      .setMimeType(ContentService.MimeType.JSON);
44    
45  } catch (error) {
46    // Return error message if something goes wrong
47    var errorMsg = {
48      status: "error",
49      message: error.toString()
50    };
51    return ContentService
52      .createTextOutput(JSON.stringify(errorMsg))
53      .setMimeType(ContentService.MimeType.JSON);
54  }
55}
56
57/**
58 * doPost(e)
59 * This function is triggered when a POST request is sent to the web app URL.
60 * In this example, we:
61 *   1) Parse the incoming JSON data
62 *   2) Insert a new row in the sheet with the expected schema
63 *   3) Return a success or error message as JSON
64 */
65function doPost(e) {
66  try {
67    // Parse incoming POST data as JSON
68    var data = JSON.parse(e.postData.contents);
69    
70    // Open spreadsheet and select sheet
71    var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
72    var sheet = ss.getSheetByName(SHEET_NAME);
73    
74    // Build a new row based on the provided schema
75    // If 'timestamp' is not provided by the client, we can generate the current time
76    var timestamp = data.timestamp ? new Date(data.timestamp) : new Date();
77    
78    // Prepare the row data in the correct order
79    // Match this order to the columns in your sheet
80    var newRow = [
81      timestamp,          // Column A: timestamp
82    /* -- change with your data schema
83      data.user_id,       
84      data.user_name,     
85      data.message,       
86    */       
87    ];
88    
89    // Append the row
90    sheet.appendRow(newRow);
91    
92    // Build a JSON response
93    var successMsg = {
94      status: "success",
95      message: "Data saved successfully",
96      data: newRow
97    };
98    
99    return ContentService
100      .createTextOutput(JSON.stringify(successMsg))
101      .setMimeType(ContentService.MimeType.JSON);
102
103  } catch (error) {
104    // Return error message if something goes wrong
105    var errorMsg = {
106      status: "error",
107      message: error.toString()
108    };
109    return ContentService
110      .createTextOutput(JSON.stringify(errorMsg))
111      .setMimeType(ContentService.MimeType.JSON);
112  }
113}
114