Update data to google sheet using MIT APP Inventor

We come with fourth and final project in this series that is update data to google sheet using mit app inventor. So We forward our final steps towards work with google sheets form mit app inventor. In this series my first post was ” Send data to google sheet from mit app inventor “. Second post was ” Read data from google sheet using MIT APP INVENTOR “. Third post was ” Delete record from google sheet using mit app inventor “.

Project Description :

This project is basically extension work of our last project. So making a project update data to google sheet using mit app inventor, we need basic concept of android app making. And some little bit knowledge about mit app inventor blocks. To build our project we use

• MIT APP INVENTOR
• Google Sheet
• App Script

In this project you will learn that how we will read single data form google sheets row wise. For that, we have to press the name of data which we want to read. When you long press the name from the list of our database, data will imported new input field. Now if we want to edit and update the data then we have to click the text field. After that we can edit those data and update it by pressing update button. Your data will be updated permanently at the google sheets. In that way you can read single data and update it very easily.

MIT APP INVENTOR

Let see what we have to do at mit app inventor. We build that project very simple with three button, one list viewer and two input text field. When we press the read all button. It set the web url. And it call the web url. When web got test as CSV. Separate it and display at list viewer. Now we select the data what we want read. Long Press the record from list. Then data will fetching from google sheet and display at input field. Here we made the two field database so we use two input field. If you want more data to display then use that much input text field. In bellow you can see the simple looks of our app.

Now talk about mit app inventor block programming. Here you can see very simple logic we used. Firstly, when page is initialise. One global variable naming URL is set with app script url. Secondly, when read all button press. It set the app script url to web1 and call it.

Thirdly, we initialise two global variable with empty list. Fourthly, when web1 got test. It separate the CSV data. And display it to list viewer. In bellow you can see the programming blocks of our project update data to google sheet using MIT APP INVENTOR.

Fifthly, when we press delete button then it set new web1 url with field selection index and delete function. And call the web1 to perform the task.

Sixth, now we learn how we can read single data. For that we use list view after picking block. Set Web2 url with global url followed by id with list view selection index. Then call the Read Record function and call Web2 to get data. When Web2 got data, it set to display the second column data at disp_name test field. And third column data set to  ids_phone text field.

Finally, now time to update and edit the data of database. For that edit the contain in text field and press update button. When update press, set Web3 url with edited data and call Web3 get. 

 Google Sheet :

Now we have to specified the google sheet. From where we want to read, edit and update the data. Mind that we have to give the permission that any one can view that sheet.  So in my project it look like that.

 App Script :

It is the main portion of our project. We have to do update data using app script. How we create that every thing shown in the video. Please watch that video. I am sure you will able to do it by own very easily.

Please find the app script at bellow.

……………CODE………………

function doGet(e) {
  return ManageSheet(e);
}
function doPost(e) {
  return ManageSheet(e);
}

function ManageSheet(e) {

  //READ ALL RECORDS
  if ( e.parameter.func == "ReadAll") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0]; 
    var rg = sh.getDataRange().getValues(); 
    var outString = '';
      for(var row=0 ; row<rg.length ; ++row){
        outString += rg[row].join(',') + '\n';  
      } 
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  }
    
   //DELETE SINGLE RECORD
   else if (e.parameter.func == "Delete") {
    var record = e.parameter.name;
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    sh.deleteRow(parseInt(record) + 1);  
    return ContentService.createTextOutput("Success");  
 } 
  //READ SINGLE RECORD
   else if ( e.parameter.func == "ReadRecord") {
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var rg = sh.getDataRange().getValues();
    var outString = '';
    outString += rg[parseInt(e.parameter.id)].join(',');
    return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
  } 
  //UPDATE SINGLE RECORD
   else if (e.parameter.func == "Update") { 
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheets()[0];
    var data = [ [ e.parameter.name, e.parameter.phone ] ]; 
    sh.getRange("B"+(parseInt(e.parameter.id)+1)+":C"+(parseInt(e.parameter.id)+1)).setValues(data);
    return ContentService.createTextOutput("Success");
  }

……………….END…………………