CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2127

Add an answer to the spreadsheet if a script error occurs

I haven't learned how to adjust so that if there is an error in the script when trying to capture data from openweathermat.org, the word ERROR is written in cell A10 on the Clima page of my spreadsheet.

I would like help and advice on how I should adjust the script to make this happen.

function TudoCompleto() {

  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Clima');
  var rg=sh.getRange("B12");
  var vA=rg.getValues();
  if (vA[0][0]=="OK"){

    var spreadsheet = SpreadsheetApp.getActive();
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName("Clima");
    var apiKey = "XXXXXXXXXXXXXXXXXXXXXXXXX";

    var cityName = sheet.getRange("A1").getValue();
    var apiCall = "api.openweathermap.org/data/2.5/weather?q=" + cityName +"&appid=" + apiKey;

    var response = UrlFetchApp.fetch(apiCall);
    var data = JSON.parse(response.getContentText());
    Logger.log(response.getContentText());

    var weather = data["weather"][0]; //It's an array
    var sys = data["sys"];
    var main = data["main"]
    var location = data["name"];
    var country = sys["country"];
    var weatherDesc = weather["main"];
    var temp = main["temp"];
    var minTemp = main["temp_min"];
    var maxTemp = main["temp_max"];

    var weatherData = [
      ["Location:", location],
      ["Country:", country],
      ["Weather:", weatherDesc],
      ["Teaperture:", temp],
      ["Min Temp:", minTemp],
      ["Max Temp:", maxTemp]
    ];

    sheet.getRange(3, 1, weatherData.length, weatherData[0].length).setValues(weatherData);

    //The API Call works
    // The Data will be retrieved like below JSON file.
    //{"coord":{"lon":139,"lat":35},
    //"sys":{"country":"JP","sunrise":1369769524,"sunset":1369821049},
    //"weather":[{"id":804,"main":"clouds","description":"overcast clouds","icon":"04n"}],
    //"main":{"temp":289.5,"humidity":89,"pressure":1013,"temp_min":287.04,"temp_max":292.04},
    //"wind":{"speed":7.31,"deg":187.002},
    //"rain":{"3h":0},
    //"clouds":{"all":92},
    //"dt":1369824698,
    //"id":1851632,
    //"name":"Shuzenji",
    //"cod":200}

    var spreadsheet = SpreadsheetApp.getActive();

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
    var sheet = spreadsheet.getSheetByName("Clima"); 

    for (var i = 1; i < 2; i++) {

      var cel = "F"+ i //

      if (sheet.getRange(cel).getValue() != "")  {   

        for (var u = 1; i < 2000; u++) {
          var cel2 = "R"+ u //

          if (sheet.getRange(cel2).getValue() == "")  { 

            sheet.getRange("R"+u).setValue(sheet.getRange(cel).getValue());
            break;
          };
        }
      }
    };

    var spreadsheet = SpreadsheetApp.getActive();

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
    var sheet = spreadsheet.getSheetByName("Clima"); 

    for (var i = 1; i < 2; i++) {

      var cel = "B"+ i //

      if (sheet.getRange(cel).getValue() != "")  {   

        for (var u = 1; i < 2000; u++) {
          var cel2 = "S"+ u //

          if (sheet.getRange(cel2).getValue() == "")  { 

            sheet.getRange("S"+u).setValue(sheet.getRange(cel).getValue());
            break;
          };
        }
      }
    };

    var spreadsheet = SpreadsheetApp.getActive();

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
    var sheet = spreadsheet.getSheetByName("Clima"); 

    for (var i = 1; i < 10; i++) {

      var cel = "H"+ i //

      if (sheet.getRange(cel).getValue() != "")  {   

        for (var u = 1; i < 2000; u++) {
          var cel2 = "M"+ u //

          if (sheet.getRange(cel2).getValue() == "")  { 

            sheet.getRange("M"+u).setValue(sheet.getRange(cel).getValue());
            break;
          };
        }
      }
    };

    spreadsheet.getRange('Clima!A12').activate();
    spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

    spreadsheet.getRange('Clima!R:S').activate();
    spreadsheet.getRange('Clima!I:J').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  };
};

In order to publish, I had to add the entire script at the request of a member to help him understand my need. I apologize for the long script.

Upvotes: 0

Views: 44

Answers (1)

Cooper
Cooper

Reputation: 64100

 function TudoCompleto() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Clima');
  var rg=sh.getRange("B12");
  var vA=rg.getValues();
  try{
    if (vA[0][0]=="OK"){
      var spreadsheet = SpreadsheetApp.getActive();
      var ss = SpreadsheetApp.getActive();
      var sheet = ss.getSheetByName("Clima");
      var apiKey = "XXXXXXXXXXXXXXXXXXXXXXXXX";
      var cityName = sheet.getRange("A1").getValue();
      var apiCall = "api.openweathermap.org/data/2.5/weather?q=" + cityName +"&appid=" + apiKey;
      var response = UrlFetchApp.fetch(apiCall);
      var data = JSON.parse(response.getContentText());
      Logger.log(response.getContentText());
      var weather = data["weather"][0]; //It's an array
      var sys = data["sys"];
      var main = data["main"]
      var location = data["name"];
      var country = sys["country"];
      var weatherDesc = weather["main"];
      var temp = main["temp"];
      var minTemp = main["temp_min"];
      var maxTemp = main["temp_max"];
      var weatherData = [
        ["Location:", location],
        ["Country:", country],
        ["Weather:", weatherDesc],
        ["Teaperture:", temp],
        ["Min Temp:", minTemp],
        ["Max Temp:", maxTemp]
      ];
      sheet.getRange(3, 1, weatherData.length, weatherData[0].length).setValues(weatherData);
      var spreadsheet = SpreadsheetApp.getActive();
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
      var sheet = spreadsheet.getSheetByName("Clima"); 
      for (var i = 1; i < 2; i++) {
        var cel = "F"+ i //
        if (sheet.getRange(cel).getValue() != "")  {   
          for (var u = 1; i < 2000; u++) {
            var cel2 = "R"+ u //
            if (sheet.getRange(cel2).getValue() == "")  { 
              sheet.getRange("R"+u).setValue(sheet.getRange(cel).getValue());
              break;
            }
          }
        }
      }
      var spreadsheet = SpreadsheetApp.getActive();
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
      var sheet = spreadsheet.getSheetByName("Clima"); 
      for (var i = 1; i < 2; i++) {
        var cel = "B"+ i //
        if (sheet.getRange(cel).getValue() != "")  {   
          for (var u = 1; i < 2000; u++) {
            var cel2 = "S"+ u //
            if (sheet.getRange(cel2).getValue() == "")  { 
              sheet.getRange("S"+u).setValue(sheet.getRange(cel).getValue());
              break;
            }
          }
        }
      }
      var spreadsheet = SpreadsheetApp.getActive();
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // planilha ativa
      var sheet = spreadsheet.getSheetByName("Clima"); 
      for (var i = 1; i < 10; i++) {
        var cel = "H"+ i //
        if (sheet.getRange(cel).getValue() != "")  {   
          for (var u = 1; i < 2000; u++) {
            var cel2 = "M"+ u //
            if (sheet.getRange(cel2).getValue() == "")  { 
              sheet.getRange("M"+u).setValue(sheet.getRange(cel).getValue());
              break;
            }
          }
        }
      }
      spreadsheet.getRange('Clima!A12').activate();
      spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
      spreadsheet.getRange('Clima!R:S').activate();
      spreadsheet.getRange('Clima!I:J').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }
  }
  catch(e) {
    ss.getSheetByName("Clima").getRange('A10').setValue('ERROR');
    Logger.log(e);
  }
}

Upvotes: 2

Related Questions