Google Sheets update from MIT and getting the response back, and processing the same in synchronous mode

[Prabu_joyous]

I am updating user status of generating a report on to google sheets, when the status is updated onto google sheet, would like to get the response back, the main thread has to wait until the response is received.. On the main thread, after web1.Get, A record will be inserted onto the SQLDB table with status PENDING, after web1.Get method, there will be a delayed loop on sqldb record status for SUCCESSS, if the loop ends without any response, then the process is stopped. At Web1.Gottext, when the response received, the same is updated onto a record in sqldb with Success..

I just want to know if the approach is OK? is there a beter way of doing?

Also, for some reason I'm not getting the gottext event fired, even when google sheets updation is succssful, however the response is not getting received into web1.gottext.. I have given the block images and my google app script..

I tested the app script url directly onto web browser, it appends the record onto google sheet and gets the response text back.. However from my MIT app, the record is appended onto google sheets, but the web1.gottext is never called..

Thank you..

Google app script:

function addUser(e, sheet) {
  var id = e.parameter.Id;
  var month = e.parameter.Month;
  sheet.appendRow([id, month]);
}

function addHistoryStatus(e, sheet) {
  if (e.parameter.Status=="Restarted"){
    sheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
  } else {
    sheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
  }
  
}

function addMonthlyReportRow(e, sheet) {
  sheet.appendRow([e.parameter.HealerID, e.parameter.HealeeID, e.parameter.Name, e.parameter.Age, e.parameter.Gender, e.parameter.TreatmentDate, e.parameter.Symptoms, e.parameter.ACUPoint, e.parameter.Month]);
}

function doGet(e) {
//  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var academySheets = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY/edit#gid=0");
  
  var statusSheet = academySheets.getSheetByName("HealerReportStatus");
  var historySheet = academySheets.getSheetByName("MonthlyReportHistory");
  
  var method = e.parameter.Method;
  if (method == "addUser") {
    addUser(e, statusSheet);
    return ContentService.createTextOutput("SuccessAddUser");  
  } if (method == "addHistoryStatus") {
    addHistoryStatus(e, historySheet);
    return ContentService.createTextOutput("SuccessAddStatus");      
  } 
  
}

function doPost(e) {
//  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var academySheets = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY/edit#gid=0");
  
  var statusSheet = academySheets.getSheetByName("HealerReportStatus");
  var historySheet = academySheets.getSheetByName("MonthlyReportHistory");
  
  var method = e.parameter.Method;
  if (method == "addUser") {
    addUser(e, statusSheet);
    return ContentService.createTextOutput("SuccessAddUser");  
  } if (method == "addHistoryStatus") {
    addHistoryStatus(e, historySheet);
    return ContentService.createTextOutput("SuccessAddStatus");  
  } 

}

[TIMAI2]

Try your script like this:

function doGet(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var statusSheet = academySheets.getSheetByName("HealerReportStatus");
  var historySheet = academySheets.getSheetByName("MonthlyReportHistory");
  var method = e.parameter.Method;
  
	if (method == "addUser") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		statusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add User");  
  
	}	elseif (method == "addHistoryStatus") {

			if (e.parameter.Status=="Restarted"){
				historySheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				historySheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Status");      
	
	} 
  
}

I have found that sometimes the Content Service does not return when calling other functions inside the doGet(e).

Also, check the capital letters being used in the construction of your url, the script will be case sensitive to parameters, i.e. "Method" and "method" are different.

[TIMAI2]

And don't forget to update your web app:

1 Open the script project
2 Go to Publish
3 Deploy as Web App
4 Project version: - select New from the dropdown
5 Execute the app as: your google account address (email)
6 Who has access to the app: Anyone, even anonymous
7 Press the Update button

You have to do this EVERY TIME you change your script

[Prabu_joyous]

Thanks @TIMAI2, I followed your instructions, the web1.GOTTEXT event is getting fired now, I'm still testing, will check and update..

[Prabu_joyous]

@TIMAI2 and Friends,

I had to change the app script to the following, then I try did follow all your steps, now MIT web1.GotText event is not getting callled.. However for each call to addition, the addition of the rows are being done successfully, but for each completion the GotText event is not getting called.. Do folks see any problem with the code? In the MIT app inventor code wise no change, as it is being done previously..

In production if we get a problem like this, then all the apps of the users will be in problem..

Thanks

function doGet(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var healerStatusSheet = academySheets.getSheetByName("HealerStatus");
  var reportStatusSheet = academySheets.getSheetByName("ReportStatus");
  var reportDataSheet = academySheets.getSheetByName("ReportData");
  var method = e.parameter.Method;
  
  if (method == "addHealer") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		healerStatusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add Healer Status");
  
  }	
  if (method == "addReportStatus") {

			if (e.parameter.Status == "Restarted") {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Report Status");
	
  } 

}
  
function doPost(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var healerStatusSheet = academySheets.getSheetByName("HealerStatus");
  var reportStatusSheet = academySheets.getSheetByName("ReportStatus");
  var reportDataSheet = academySheets.getSheetByName("ReportData");
  var method = e.parameter.Method;
  
  if (method == "addHealer") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		healerStatusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add Healer Status");
  
  }	
  if (method == "addReportStatus") {

			if (e.parameter.Status == "Restarted") {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Report Status");
	
  } 
  
}

[TIMAI2]

Looking back at your blocks you appear to be using HTTP GET to submit the url

1. therefore you do not need the doPost(e) code in your script

2. you need to make "one" function. The two if statements may be cancelling each other out. Look back at my example where I used elseif. Because you only have two statements you could use else.

function doGet(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var healerStatusSheet = academySheets.getSheetByName("HealerStatus");
  var reportStatusSheet = academySheets.getSheetByName("ReportStatus");
  var reportDataSheet = academySheets.getSheetByName("ReportData");
  var method = e.parameter.Method;
  
  if (method == "addHealer") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		healerStatusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add Healer Status");
  
  } else if (method == "addReportStatus") {

			if (e.parameter.Status == "Restarted") {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Report Status");
	
  } 
}

3. try running a GET request in your computer browser to see if you get a response (you can get most of the url if you run Do It on your url construction blocks)

[Prabu_joyous]

@TIMAI2, Thanks for your reply..

https://script.google.com/macros/s/AKfycbxgjJpsZitpU2fYLOKxLW2BCSoaJhFiClenPoFJ_W-OOyUKS_KW/exec?Method=addReportStatus&Status=Restarted&HealerId=2020_08_23_11_56_50_181_924_134&OldHealerId=2020_07_14_07_43_03_969_10_552&Month=2020/05

I run above URL on my browser, I'm getting the response back on the browser..

But through our MIT app, the response event GOTTEXT is not invoked.. The script is running fine to append the row on the google sheet, but response alone not getting invoked..

function doGet(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var healerStatusSheet = academySheets.getSheetByName("HealerStatus");
  var reportStatusSheet = academySheets.getSheetByName("ReportStatus");
  var reportDataSheet = academySheets.getSheetByName("ReportData");
  var method = e.parameter.Method;
  
  if (method == "addHealer") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		healerStatusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add Healer Status");
  
  }	else if (method == "addReportStatus") {

			if (e.parameter.Status == "Restarted") {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Report Status");
	
  } 

}


function doPost(e) {

  var academySheets = SpreadsheetApp.openById("1gHwc-U83KoUW-LNKONHrNe6Vx5hnZ0uPcDsxA3xGEEY");
  var healerStatusSheet = academySheets.getSheetByName("HealerStatus");
  var reportStatusSheet = academySheets.getSheetByName("ReportStatus");
  var reportDataSheet = academySheets.getSheetByName("ReportData");
  var method = e.parameter.Method;
  
  if (method == "addHealer") {
		var id = e.parameter.Id;
		var month = e.parameter.Month;
		healerStatusSheet.appendRow([id, month]);
	
	return ContentService.createTextOutput("Success Add Healer Status");
  
  }	else if (method == "addReportStatus") {

			if (e.parameter.Status == "Restarted") {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status, e.parameter.OldHealerId]);
			} else {
				reportStatusSheet.appendRow([e.parameter.HealerId, e.parameter.Month, e.parameter.Status]);
			}
          
	return ContentService.createTextOutput("Success Add Report Status");
	
  } 
  
}

I have not removed doPost method yet..

Am I missing anything, why this feature has to be this difficult.. Because of this road block, my app development is delayed for long time from going into production..

I ensured the google account is correct, the URL link given is what is been used.. I checked it correctly.. What makes the web response binding with in web calls from MIT app inventor..

Please help me..

Thank you..

[TIMAI2]

Appears that your web app is working correctly, so a problem in the blocks somewhere.

Try this test aia project ( I used your posted url to test). This returns the responseContent.

You might want to try these blocks in your project to see what is being returned ?

testWebGotText.aia (2.1 KB)

[Prabu_joyous]

Thanks @TIMAI2. Yes my code has an issue.. It is not working as I expect..

I want to process each web request, on synchronus way. I can proceed to next request only when I get a response, either success or failure..

I expect Web1.Get be in different thread and Web.GotText will be called in an event driven mode.. I wanted to make synchronus call, so after Web1.Get, I have a loop until status for the current web request is received in the event of Web1.Gottext.. A month back When i tried the same code, it was working, but now it is not working in my synchronus logic.. Any conceptual error, please check and let me know friends.. Thanks for your help.. Please see the code block now I changed, it is not working as expected..

[Taifun]

@Prabu_joyous

I want to process each web request, on synchronus way. I can proceed to next request only when I get a response, either success or failure..

see this example about how to do it App Inventor Tutorials and Examples: Webprefetch File by File | Pura Vida Apps

Taifun

---

Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by Taifun.

[TIMAI2]

an example using the same method as Taifun linked to, but fetching responseContent not files.

Interval between each action

Try like this, it won't start the next download until the previous one has returned (no five second delay but I believe achieves what you are looking to do ?) [image] Could not see from your blocks if you were getting content or files, so went for content!

(see note about setting lists to empty if running again and again)