A basic custom Google Sheets script that adds weather to our holiday sheet.

script.js

/**
 * Update the protected weather column.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  
  var menuItems = [
    {
      name: "Update weather column",
      functionName: "updateWeather"
    }
  ];
  
  spreadsheet.addMenu("Weather", menuItems);
}


/**
 * Ask for a column and update the weather in it.
 */
function updateWeather() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  var columns = getColumns();  
  
  if (columns) {
    var range = sheet.getRange("Weather");
    
    Logger.log(range.getRow());
    
    for (var i = 1, rows = range.getNumRows(); i <= rows; i++) {
      var row = sheet.getRange(range.getRow() + i - 1, 1, 1, 10).getValues();
      var date = row[0][columns.date - 1];
      var latitude = row[0][columns.latitude - 1];
      var longitude = row[0][columns.longitude - 1];
      var number = row[0][columns.number - 1];
      
      var url = WEATHER_URL(date, number);
      var weather = WEATHER(date, latitude, longitude);
      
      var value = Utilities.formatString("=HYPERLINK(\"%s\", \"%s\")", url, weather);
      
      range.getCell(i, 1).setValue(value);
    }
  }
}


function getColumns() {
  var columns = {};

  // Prompt the user for a column number.
  columns.date = Browser.inputBox("Update weather",
      "Please enter the column number of the date (for example, \"1\"):",
      Browser.Buttons.OK_CANCEL);

  if (columns.date == "cancel") {
    return;
  }

  // Prompt the user for a column number.
  columns.latitude = Browser.inputBox("Update weather",
      "Please enter the column number of the latitude (for example, \"5\"):",
      Browser.Buttons.OK_CANCEL);

  if (columns.latitude == "cancel") {
    return;
  }

  // Prompt the user for a column number.
  columns.longitude = Browser.inputBox("Update weather",
      "Please enter the column number of the longitude (for example, \"6\"):",
      Browser.Buttons.OK_CANCEL);

  if (columns.longitude == "cancel") {
    return;
  }

  // Prompt the user for a column number.
  columns.number = Browser.inputBox("Update weather",
      "Please enter the column number of the AccuWeather number (for example, \"7\"):",
      Browser.Buttons.OK_CANCEL);

  if (columns.number == "cancel") {
    return;
  }
  
  return columns;
}


/**
 * A custom function for returning weather data from Forecast.io.
 *
 * @param {String} date The date of the weather
 * @param {Number} latitude The latitude of a location
 * @param {Number} longitude The longitude of a location
 * @returns {String} The weather summary and high/low temperatures
 */
function WEATHER(date, latitude, longitude) {  
  if (typeof date !== "string" && typeof latitude !== "number" && typeof longitude !== "number") {
    return "Arguments are incorrect.";
  }
  
  var apiUrl = createApiUrl(date, latitude, longitude);
  var data = getWeather(apiUrl);
  
  return renderWeatherStatus(data);
}



/**
 * A custom function for returning weather data from Forecast.io.
 *
 * @param {String} date The date of the weather
 * @param {Number} latitude The latitude of a location
 * @returns {String} The weather URL to Accuweather
 */
function WEATHER_URL(date, number) {
  if (typeof date !== "string" && typeof number !== "number") {
    return "Arguments are incorrect.";
  }
  
  var accuweatherUrlTemplate = "http://www.accuweather.com/en/jp/japan/%s/daily-weather-forecast/%s?day=%s";
  
  var then = new Date(date);
  var now = new Date(); 
  
  var day = 24 * 60 * 60 * 1000;
  var offset = 1;  
  var diff = Math.round(Math.abs((now.getTime() - then.getTime()) / day)) + offset;
  
  var accuweatherUrl = Utilities.formatString(accuweatherUrlTemplate, number, number, encodeURIComponent(diff));
    
  return accuweatherUrl;
}


/**
 * A custom function for returning the weather API URL from Forecast.io.
 *
 * @param {String} date The date of the weather
 * @param {Number} latitude The latitude of a location
 * @param {Number} longitude The longitude of a location
 * @returns {String} The URL of the weather API
 */
function WEATHER_API_URL(date, latitude, longitude) {  
  if (typeof date !== "string" && typeof latitude !== "number" && typeof longitude !== "number") {
    return "Arguments are incorrect.";
  }
  
  return createApiUrl(date, latitude, longitude);
}


/**
 * Returns the weather API URL from Forecast.io.
 *
 * @param {String} date The date of the weather
 * @param {Number} latitude The latitude of a location
 * @param {Number} longitude The longitude of a location
 * @returns {String} The URL of the weather API
 */
function createApiUrl(date, latitude, longitude) {
  var apiKey = "";
  
  var dateUTM = Utilities.formatDate(date, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
  var apiUrl = Utilities.formatString("https://api.forecast.io/forecast/%s/%s,%s,%s?units=si&exclude=currently,flags,hourly", apiKey, latitude, longitude, dateUTM);
  
  Logger.log(apiUrl);
  
  return apiUrl;
}


/**
 * Gets the weahter from Forecast.io.
 *
 * @param {String} apiUrl The API URL to fetch
 * @returns {Object} A JSON object returned from the API
 */
function getWeather(apiUrl) {
  try {    
    var response = UrlFetchApp.fetch(apiUrl);
  
    Logger.log(response);
    
    return JSON.parse(response.getContentText());    
  } catch (e) {
    Logger.log(e);
  }
  
  return "UrlFetchApp failed.";
}


/**
 * Render the daily weather forecast as a string.
 *
 * @param {Object} data A JSON object with the daily weather forecast
 * @returns {String} The weather summary and high/low temperatures
 */
function renderWeatherStatus(data) {
  var daily = data.daily.data[0];
  
  var summary = daily.summary.replace(" throughout the day", "");
  var max = Math.round(daily.temperatureMax);
  var min = Math.round(daily.temperatureMin);
  
  return Utilities.formatString("%s %s° / %s°", summary, max, min);;
}