Created
April 20, 2022 07:49
-
-
Save coucoseth/1a34299a28dd45cd0f5ab798e5eae592 to your computer and use it in GitHub Desktop.
move data from google sheet to mysql database on localhost
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function myFunction() { | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getSheetByName("MVL"); // MVL is the name of sheet in google sheet doc | |
| // collecting data from 2nd Row , 1st column to last row and last column | |
| var rows = sheet.getRange(2,1,sheet.getLastRow()-1, sheet.getLastColumn()).getValues(); | |
| var jo = {}; | |
| var dataArray = []; | |
| Logger.log(rows.length) | |
| for(var i = 1; i< rows.length ; i++){ | |
| var dataRow = rows[i]; | |
| var record = {}; | |
| if (dataRow[0] == false) { | |
| record['Make'] = dataRow[1]; | |
| record['Model'] = dataRow[2]; | |
| record['BodyStyle'] = dataRow[4]; | |
| record['Engine'] = dataRow[7]; | |
| dataArray.push(record); | |
| // change color rows | |
| for(var k = 1; k < 10; k++) { | |
| ss.getSheetByName("MVL").getRange(i+2,1).setValue(''); | |
| ss.getSheetByName("MVL").getRange(i+2, k).setBackground("#00FF00"); | |
| //ss.getSheetByName("Sheet1").getRange(i+2, k).clearContent(); | |
| } | |
| } | |
| } | |
| jo = dataArray; | |
| var result = JSON.stringify(jo); | |
| // Logger.log(result); | |
| var options = { | |
| 'method' : 'post', | |
| 'contentType': 'application/json', | |
| // Convert the JavaScript object to a JSON string. | |
| 'payload' : JSON.stringify(jo) | |
| }; | |
| var response = UrlFetchApp.fetch('https://13dd-41-173-2-183.ngrok.io/postData.php', options); | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| $con = mysqli_connect('localhost', 'root', 'dbPwd', 'dbName'); | |
| if (!$con) { | |
| die('Could not connect: ' . mysql_error()); | |
| } | |
| // JSON string | |
| $someJSON = file_get_contents("php://input"); | |
| // Convert JSON string to Array | |
| $someArray = json_decode($someJSON, true); | |
| // print_r($someArray); // Dump all data of the Array | |
| foreach ($someArray as $mydata) { | |
| // Use $field and $value here | |
| echo "\n"; | |
| //echo $mydata['banfn']; | |
| $query = "INSERT INTO sheet (Make, Model, BodyStyle, Engine) VALUES('".$mydata['Make']."','".$mydata['Model']."','".$mydata['BodyStyle']."','".$mydata['Engine']."')"; | |
| mysqli_query($con, $query); | |
| } | |
| $con->close(); | |
| ?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment