writeGoogleSheetsToFirebase.googleScript

function writeDataToFirebase() {

  // Get individual rainfall entries.
  var ss = SpreadsheetApp.openById("1MZLjW7YuG9UKPpIjN2w8jjnubT74oiAbvInntqNTQ5E");
  var sheet = ss.getSheets()[0];
  var data = sheet.getDataRange().getValues();
  var dataToImport = {};

  // The following values should be added to Project Settings -> Script Properites as variables.
  var serviceAccountEmail = PropertiesService.getScriptProperties().getProperty('SERVICE_ACCOUNT_EMAIL');
  var privateKey = PropertiesService.getScriptProperties().getProperty('PRIVATE_KEY');
  privateKey = privateKey.replace(/\\n/g, '\n');

  var authToken = getAuthToken(serviceAccountEmail, privateKey);

  for(var i = 1; i < data.length; i++) {

    var programName = data[i][1];
    var programKey = data[i][2];

    programName = i + "-";

    // Ignore row if no key is present.
    if (programKey == "") {
      break;
    }

    dataToImport[programName] = {};

    // Define which columns contain data which should be ignored (e.g. private fields).
    skipColumns=['timestamp', 'email', 'failureToCollectData'];

    for(var y = 0; y < 6; y++) {
      var columnName = data[0][y]
      if ((columnName) && (columnName.length > 0)) {
        columnName = toTitleCase(columnName).replace(/[\s\(\)\?]/g, '');
        columnName = decapitalizeFirstLetter(columnName)
        // Don't send certain columns.
        if (skipColumns.indexOf(columnName) == -1) {
          dataToImport[programName][columnName] = data[i][y];
        }
      }
    }
  }

  // Get sites data.
  var sites = {}

  var sheet = ss.getSheets()[1];
  var data = sheet.getDataRange().getValues();

  for(var i = 2; i < data.length; i++) {
    var programName = data[i][1];
    var programKey = data[i][2];

    programName = i + "-";

    // Ignore row if no key is present.
    if (programKey == "") {
      break;
    }

    sites[programName] = {};

    skipColumns=['owner', 'email', 'address'];
    for(var y = 0; y < 100; y++) {
      var columnName = data[0][y]
      if ((columnName) && (columnName.length > 0)) {
        columnName = toTitleCase(columnName).replace(/[\s\?]/g, '');
        columnName = decapitalizeFirstLetter(columnName)
        // Don't send certain columns.
        if (skipColumns.indexOf(columnName) == -1) {
          sites[programName][columnName] = data[i][y];
        }
      }
    }
  }

  var dataComplete = {
    sites: sites,
    rainData: dataToImport
  };

  var firebaseUrl = "https://rainfall-data-536ee.firebaseio.com/";
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, authToken);
  base.setData("", dataComplete);
}

function toTitleCase(str) {

    return str.replace(/\w\S*/g, function(txt){
        return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
    });
}

function decapitalizeFirstLetter(string) {
    return string.charAt(0).toLowerCase() + string.slice(1);
}

function getAuthToken(serviceAccountEmail, privateKey) {
  var header = {
    "alg": "RS256",
    "typ": "JWT"
  };

  var now = Math.floor(new Date().getTime() / 1000);
  var payload = {
    "iss": serviceAccountEmail,
    "sub": serviceAccountEmail,
    "aud": "https://oauth2.googleapis.com/token",
    "iat": now,
    "exp": now + 3600,
    "scope": "https://www.googleapis.com/auth/firebase.database https://www.googleapis.com/auth/userinfo.email"
  };

  var unsignedToken = Utilities.base64EncodeWebSafe(JSON.stringify(header)) + "." + Utilities.base64EncodeWebSafe(JSON.stringify(payload));
  var signature = Utilities.computeRsaSha256Signature(unsignedToken, privateKey);
  var signedToken = unsignedToken + "." + Utilities.base64EncodeWebSafe(signature);

  var tokenResponse = UrlFetchApp.fetch('https://oauth2.googleapis.com/token', {
    method: 'post',
    payload: {
      grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
      assertion: signedToken
    }
  });
  var token = JSON.parse(tokenResponse.getContentText());
  return token.access_token;
}