0

I am trying to achieve send sms whenever google spreadsheet gets updated with mobile no. but its taking too long to execute, surely I am making any mistake but can't find out the one. Requesting your help. below is my script.

function denver() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.setActiveSheet(ss.getSheetByName("Form Responses 1"));
    var sheet = SpreadsheetApp.getActiveSheet();
    var dataRange = sheet.getRange("A2:M1000");
    var data = dataRange.getValues();
    for (i in data) {
        var rowData = data[i];
        var contactx = rowData[5];
        var textx = rowData[8];
        var Decision = rowData[12];
        var EMAIL_STATUS = rowData[28];

        var messages_url = "https://49.50.67.32/smsapi/httpapi.jsp? 
        username = maruti & password = maruti123 & from = MARUTI & to = " + contactx + " & text = " + 
        textx + "&coding=0&flash=2";
        var options = {
            "method": "post",
        };

        options.headers = {
            "Authorization": "Basic " + Utilities.base64Encode("maruti:MARUTI")
        };

        UrlFetchApp.fetch(messages_url, options);
    }
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
shreedhar halli
  • 1
  • 1
  • How many rows do you have in the Google Sheets? Also, which do you need range of that size (A2:M1000) ? – Andreas Dec 11 '18 at 08:45
  • Thanks for the reply. there are 1000 rows I need to use. the size will be (A2:M1000) – shreedhar halli Dec 11 '18 at 08:53
  • Can you check how long each of this `messages_url` request takes time? That might be what makes your program takes longer to execute – Andreas Dec 11 '18 at 08:54
  • I have checked the same if I reduce the size then its taking smaller time to execute. – shreedhar halli Dec 11 '18 at 08:58
  • So, this `UrlFetchApp.fetch(messages_url, options);` statement takes no time to execute? – Andreas Dec 11 '18 at 08:59
  • Yes. but I have same data size in another application and its working fine. – shreedhar halli Dec 11 '18 at 09:02
  • 2
    If you aren't rate limited by this external API, consider using `UrlFetchApp#fetchAll` (it is asynchronous) in batches. Consult documentation. Also, combining `for .. in..` enumeration with arrays that should be *iterated* is not recommended. https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea – tehhowch Dec 11 '18 at 12:08

1 Answers1

0

You're using a for loop over a section of the spreadsheet that seems very large, A2:M1000, and seem to be performing a number of memory accesses on each interation, similar with in. Iterating over data can be very time consuming and very likely negatively affects your run time, I'd be hesitant to use a for loop over a large data set. Fetch will also increase your run time as it takes longer to retrieve the information you're asking for. If I were you I would break this into separate functions that look at more specific ranges

akajanedoe
  • 15
  • 8