{"id":6043,"date":"2025-01-22T03:58:48","date_gmt":"2025-01-22T03:58:48","guid":{"rendered":"https:\/\/obsidiansofteducation.com\/?page_id=6043"},"modified":"2025-01-22T04:02:49","modified_gmt":"2025-01-22T04:02:49","slug":"update-google-sheet-mit-app-inventor","status":"publish","type":"page","link":"https:\/\/obsidiansofteducation.com\/index.php\/update-google-sheet-mit-app-inventor\/","title":{"rendered":"Create, Read, Update and Delete Google Sheet in MIT App Inventor"},"content":{"rendered":"\n

Hello friends and welcome to Obsidian Soft<\/p>\n\n\n\n

In my previous tutorials on Google Sheets, I showed you how to easily write to a Google sheet using forms and also read the entire sheet into the MIT App Inventor app. This was a simple and easy approach that came with limitations. We couldn\u2019t update the rows or delete rows programmatically. We also couldn\u2019t differentiate between different sheets in our spreadsheet. In this tutorial, which I will try my best to simplify, we will not just create rows in Google Sheets but also delete or update a particular row through our MIT App Inventor app. We will also use sheet names so that if our spreadsheet has multiple sheets, we can do our operations on a particular sheet.<\/p>\n\n\n\n

So, let\u2019s begin. First of all, let\u2019s make a google sheet:<\/p>\n\n\n\n

https:\/\/docs.google.com\/spreadsheets\/u\/0<\/strong><\/a><\/p>\n\n\n\n

Make sure that you are logged in using your Gmail account. Click on the + button to create a blank spreadsheet.<\/p>\n\n\n\n

Add some headers in the first row of the sheet: ID<\/strong>, Name<\/strong>, City<\/strong><\/p>\n\n\n\n

Also, rename the sheet1 to Customers<\/strong>. Add some sample data to it too.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now, look at the address bar in your browser. You will have the spreadsheet\u2019s URL there:<\/p>\n\n\n\n

https:\/\/docs.google.com\/spreadsheets\/d\/1n5kGHrAhhIhYph0c-tRedRtlILqv8aRCEyYtDeDnBJ<\/strong>\/edit?gid=0#gid=0<\/p>\n\n\n\n

The highlighted part between d\/ and \/edit is the id of your spreadsheet. Please don\u2019t use mine. You have to create your own spreadsheet. Copy and paste this URL in some text editor like notepad as this is our spreadsheet id.<\/p>\n\n\n\n

Now, navigate to Extensions > Apps Script<\/strong> in the spreadsheet menu. A new browser window will open up.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Remove the code from it. Copy and paste the following code in it but remember to update your spreadsheet id with the one that you got from the browser URL and that you had copied and pasted in Notepad. You have to do it for both doGet and doPost functions in this script. Also, you will have to update the script for your parameters. For example, I have id, name, and city but if you have different parameters, change it accordingly. I will explain more about this change later.<\/p>\n\n\n\n

Don\u2019t forget to update the spreadsheet id for both doGet and doPost functions.<\/p>\n\n\n\n

function doGet(e) {\n\n    const sheetName = e.parameter.sheetName || \"DataSheet\";\n\n    const spreadsheet = SpreadsheetApp.openById(\"your-spreadsheet-id\"); \/\/ Use your sheet ID\n\n    const sheet = spreadsheet.getSheetByName(sheetName);\n\n    if (!sheet) {\n\n        return ContentService.createTextOutput(JSON.stringify({\n\n            status: \"error\",\n\n            action: \"read\",\n\n            message: \"Sheet not found\"\n\n        }));\n\n    }\n\n    const range = sheet.getDataRange();\n\n    const values = range.getValues();\n\n    return ContentService.createTextOutput(JSON.stringify({\n\n        status: \"success\",\n\n        action: \"read\",\n\n        data: values\n\n    }));\n\n}\n\nfunction doPost(e) {\n\n    const data = JSON.parse(e.postData.contents);\n\n    const sheetName = data.sheetName || \"DataSheet\";\n\n    const spreadsheet = SpreadsheetApp.openById(\"your-spreadsheet-id\"); \/\/ Use your sheet ID\n\n    const sheet = spreadsheet.getSheetByName(sheetName);\n\n    if (!sheet) {\n\n        return ContentService.createTextOutput(JSON.stringify({\n\n            status: \"error\",\n\n            action: data.action,\n\n            message: \"Sheet not found\"\n\n        }));\n\n    }\n\n    if (data.action === \"add\") {\n\n        sheet.appendRow([data.id, data.name, data.city]);\n\n        return ContentService.createTextOutput(JSON.stringify({\n\n            status: \"success\",\n\n            action: \"add\",\n\n            message: \"Row added successfully\"\n\n        }));\n\n    } else if (data.action === \"update\") {\n\n    const range = sheet.getDataRange();\n\n    const values = range.getValues();\n\n    let rowFound = false;  \/\/ To track if the row is found\n\n    for (let i = 1; i < values.length; i++) {\n\n      if (String(values[i][0]).trim() === String(data.id).trim()){\n\n            \/\/ Update the name and city\n\n            sheet.getRange(i + 1, 2, 1, 2).setValues([[data.name, data.city]]);\n\n            rowFound = true;\n\n            return ContentService.createTextOutput(JSON.stringify({\n\n                status: \"success\",\n\n                action: \"update\",\n\n                message: \"Row updated successfully\"\n\n            }));\n\n        }\n\n    }\n\n    \/\/ If no matching row is found\n\n    if (!rowFound) {\n\n        return ContentService.createTextOutput(JSON.stringify({\n\n            status: \"error\",\n\n            action: \"update\",\n\n            message: \"Row not found\"\n\n        }));\n\n    }\n\n}\n\n     else if (data.action === \"delete\") {\n\n        const range = sheet.getDataRange();\n\n        const values = range.getValues();\n\n        for (let i = 1; i < values.length; i++) {\n\n           if (String(values[i][0]).trim() === String(data.id).trim()){\n\n                sheet.deleteRow(i + 1);\n\n                return ContentService.createTextOutput(JSON.stringify({\n\n                    status: \"success\",\n\n                    action: \"delete\",\n\n                    message: \"Row deleted successfully\"\n\n                }));\n\n            }\n\n        }\n\n        return ContentService.createTextOutput(JSON.stringify({\n\n            status: \"error\",\n\n            action: \"delete\",\n\n            message: \"Row not found\"\n\n        }));\n\n    }\n\n    return ContentService.createTextOutput(JSON.stringify({\n\n        status: \"error\",\n\n        action: data.action,\n\n        message: \"Invalid action\"\n\n    }))\n\n}<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
You can see in the following screenshot that I have copied pasted the code and updated the spreadsheet id. I have also renamed the project to Web App for MIT App Inventor.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now, click on the deploy button.<\/p>\n\n\n\n
Choose New deployment<\/strong>.<\/p>\n\n\n\n
Choose Web app<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Set the Execute as<\/strong> field to Me<\/strong>.<\/p>\n\n\n\n
Set the Who has access<\/strong> field to Anyone <\/strong>and click on Deploy <\/strong>button on the dialog.<\/p>\n\n\n\n
Since it is a new deployment, a new authorization dialog will pop up. Click on the Authorize access<\/strong> button. <\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
You will be asked to choose a Google Account. Choose it and click on the Advanced link on the dialog that pops up.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Click on the Go to \u201cproject name of your web app\u201d. I had renamed it to \u201cWeb App for MIT App Inventor\u201d. If you haven\u2019t renamed it, it will say \u201cUntitled Project\u201d.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Another dialog will pop up. Scroll down and click on Allow button.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Copy and paste the web app URL into a text editor like notepad as we will need it later.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
(At any time, if you make a change to the web app script, you have to click on deploy button again and and choose manage deployments and save changes) <\/p>\n\n\n\n
Now, Open up MIT App Inventor and start a new project. Name it CRUDGoogleSheet.<\/p>\n\n\n\n
Drag and drop a vertical arrangement from the Layouts palette onto the viewer. Rename this arrangement to mainScreen. Make width and height both fill parent.<\/p>\n\n\n\n
Drag a listview from the user interface palette onto this mainScreen vertical arrangement and in its properties, make width: fill parent.<\/p>\n\n\n\n
Add a horizontal arrangement from layouts below it. Make align horizontal and align vertical both center. Make height 15% and width fill parent. Drag and drop a button from the user interface palette inside this horizontal arrangement. Rename to addBtn. Make the background color blue, font bold, font size 20, width 30%, shape rounded and text: Add. Select the button and duplicate it by pressing Ctrl + C and Ctrl + V on the keyboard for windows OR Command + C and Command + V on the keyboard for MacOS. Rename it to updateBtn and change text to \u201cUpdate\u201d. Duplicate again and rename it to deleteBtn and change text to \u201cDelete\u201d. Now, select the mainScreen vertical arrangement and in its properties, uncheck the \u201cVisible\u201d checkbox.<\/p>\n\n\n\n
Now, add another vertical arrangement onto the viewer. Rename it to addScreen. Make width and height fill parent. This is the concept of virtual screens. There is no need to pass data between two screens as we are technically still on the same screen. <\/p>\n\n\n\n
Drag and drop a horizontal arrangement onto this addScreen vertical arrangement. Make align horizontal and align vertical both center. Make height 15% and width: fill parents.<\/p>\n\n\n\n
Add a label from user interface to this horizontal arrangement. Rename it to idLbl. And change text to \u201cID:\u201d.  Add a textbox from the user interface on the right of the label. Rename it to idTxt. <\/p>\n\n\n\n
Select the horizontal arrangement, duplicate it and update it for Name. Do the same thing for city<\/p>\n\n\n\n
Duplicate horizontal arrangement again and remove the label and textbox from it. Add a button. Rename it to saveBtn. Give it the same properties as the ones that you gave to the addBtn. Change the text to \u201cSave\u201d. Select the button. Duplicate it and rename it to cancelBtn and change the text to \u201cCancel\u201d.<\/p>\n\n\n\n
Drag a web component from the Connectivity palette onto the viewer. Also, drag a notifier from the user interface onto the viewer.<\/p>\n\n\n\n
Now, select the addScreen arrangement and make it invisible by unchecking the visible checkbox.<\/p>\n\n\n\n
Make the mainScreen visible by checking the visible checkbox.<\/p>\n\n\n\n
Our screen design is done so let\u2019s go to the blocks section.<\/p>\n\n\n\n
Make a global variable for sheetData (an empty list in the beginning)<\/p>\n\n\n\n
Another variable sheetName and set it to your sheetName (in my case, it is \u201cCustomers\u201d).<\/p>\n\n\n\n
Add a status variable called action (this will play a role in updating, adding, and deleting). Also, add a global variable for webAppURL and set it to your webAppURL that you had copied earlier.<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
Get the screen1\u2019s initialize event.<\/p>\n\n\n\n
Make addScreen visible to false and mainScreen visible to true. This is an extra step to make sure that the correct screen is visible when the app is started.<\/p>\n\n\n\n
Click on the web1 component and get its setUrl block.<\/p>\n\n\n\n
Set it to a join block with 3 slots. You can click on the cog wheel to add more slots.<\/p>\n\n\n\n
Put the get block for webAppURL in the first slot. In the second slot, add a text block with this: <\/p>\n\n\n\n
?sheetName=<\/strong><\/p>\n\n\n\n
In the third slot, plug in the get block for the global variable sheetName. After this call Web1.Get.<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
In web1. gotText event, responseContent contains the response returned by the Web app. Make one local variable for responseDict. Use Web1\u2019s procedure JsonTextDecodeWithDictionaries to decode the ResponseContent and store it inside the local variable responseDict.<\/p>\n\n\n\n
Web1.gotText event is triggered after get or post call to the web app so we need to differentiate between reading sheet data and updating\/adding\/deleting. Use an if\/else block from control to check if the value for key \u201caction\u201d inside responseDict is \u201cread\u201d.<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
 If it is \u201cread\u201d then use a for each item loop from control to go through the value returned against key: \u201cdata\u201d inside our responseDict which is actually a list of lists. For each item (row) in this data, get cell data and join it all using \u201c-\u201d to make one long text item and add that item to the sheetData list.<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
After the for loop, check if sheetData isn\u2019t empty. It will be empty if the sheet is completely blank (not even column names) or if there is some error. If it isn\u2019t empty, remove the first item from SheetData as these are the column names from the sheet i.e. ID, Name, and City. Then, provide this updated SheetData list to the listview\u2019s elements. <\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
At the end of the if\/else block, add another if for checking for any message from the web app, and if there is any, use the notifier to show this message. So, our web1.gotText looks like this now:<\/p>\n\n\n\n
\"\"\/<\/figure>\n\n\n\n
Try the app now and it should read the sample data from Google spreadsheet and show an error if we provide the wrong sheetName.<\/p>\n\n\n\n
Before working on our add, update and delete, let\u2019s look at our web app script again. You can open it again by navigating to Extensions > Apps Script<\/strong> in the spreadsheet menu.<\/p>\n\n\n\n