MC Works 32 to Cloud (Google Sheet)
Write data from tags of MC Works 32 Scada (Mitsubishi Scada) to Cloud on Google Sheet
1) Open Google drive and right click >> Select Google sheets >> Blank Spreadsheet
2. Once the Sheet is open, Rename accordingly, In my case its “ScadaLog”
3. Mention Title name in first Row according to the column used.
Click Tools >> Script Editor
4. Once the Script Editor open, Add Below Logic,
5.Go back to Sheet and Copy the Spreadsheet Id, as shown below.
6. Go to Script Editor and replace the Spreadsheet Id on line no.9 as shown below.
7. Go to Publish >> Deploy as web app.
8. Use premission as anyone, and Deploy.
9. Allow and continue with the process.
10. Just copy Current web app url and save it somewhere.
11. Open Graph Works 32 , Add 2 process points, Set object name as Data1 and Data2 respectively.
12. Insert ActiveX Control/OLE Object.
13. Add Microsoft Web Browser
14. Add Microsoft Forms Command Button
15. Right Click on Command Button and Ciew VBA Code
16. Right Click on Command Button on graphworks page >> Click View VBA Code, and add below logic,
17. Now run the project in Runtime.
Every click on command button will log a data to google excel sheet.
////////////////////////////////////////////////Google Script////////////////////////////////////////////////
function doGet(e) {
Logger.log( JSON.stringify(e) ); // view parameters
var result = 'Ok'; // assume success
if (e.parameter == 'undefined') {
result = 'No Parameters';
}
else { var sheet_id = '1IedK3S8TG6bVtfJIYWDkNLLR1KiPZA_rrUVFg0'; // Spreadsheet ID
var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet(); // get Active sheet
var newRow = sheet.getLastRow() + 1;
var rowData = [];
rowData[0] = new Date(); // Timestamp in column A
for (var param in e.parameter) {
Logger.log('In for loop, param=' + param);
var value = stripQuotes(e.parameter[param]);
Logger.log(param + ':' + e.parameter[param]);
switch (param) {
case 'Data1': //Parameter
rowData[1] = value; //Value in column B
result = 'Written on column B';
break;
case 'Data2': //Parameter
rowData[2] = value; //Value in column C
result += ' ,Written on column C';
break;
default:
result = "unsupported parameter";
}
}
Logger.log(JSON.stringify(rowData));
// Write new row below
var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
newRange.setValues([rowData]);
}
// Return result of operation
return ContentService.createTextOutput(result);
}
/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
return value.replace(/^["']|['"]$/g, "");
}
///////////////////////////////////////// MC Works 32 VBA Script /////////////////////////////////////////
Private Sub Send_Click()
Dim x, y, f, a, b As String
x = ThisDisplay.GetPointObjectFromName(ThisDisplay.GetDynamicObjectFromName("Data1").dataSource).Value
y = ThisDisplay.GetPointObjectFromName(ThisDisplay.GetDynamicObjectFromName("Data2").dataSource).Value
t = "https://script.google.com/macros/s/AKfycbzyzSKy6Hz5pl0HVKBPlBfVp-jGYZzChRxdhMXmyIlvfFfypyI/exec?"
f = t & "Data1=" & x & "&Data2=" & y
WebBrowser1.Navigate (f)
End Sub