google-apps-script - 如果发生脚本错误，请在电子表格中添加答案

Question

我还没有学会如何调整，以便在尝试从 openweathermat.org 捕获数据时脚本中出现错误，这个词ERROR会写在我的电子表格页面A10上的单元格中。Clima

我需要有关如何调整脚本以实现这一点的帮助和建议。

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);
  };
};

为了发布，我必须应会员的要求添加整个脚本，以帮助他了解我的需求。我为冗长的脚本道歉。

标签： google-apps-scriptgoogle-sheets

Answer 1

 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);
  }
}