data_ingestion_script.gs

// this function createes a firebase menu
function onOpen() {
  SpreadsheetApp.getUi().createMenu('🔥 Firebase Menu').addItem('Export to firestore', 'main').addToUi();
}


function main(){
  // get the name of the current spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetName = sheet.getName();
  var d = sheet.getDataRange().getValues();
  
  var config = {
    'client_email' : '',
    'private_key' : '',
    'project_id' : ''
  };


  // get the first row for properties 
  var properties = getProperties(sheet);

  // get records 
  var records = getRecords(sheet);

  // configure service account email, key, projectId
  var firestore = FirestoreApp.getFirestore(config.client_email, config.private_key, config.project_id);

  exportToFirestore(firestore, sheetName, properties, records);
}

function getProperties(sheet) {
  var lastCol = sheet.getLastColumn(); // get the last col
  return sheet.getRange(1,1, 1,lastCol).getValues()[0].filter(String) //first row, first col, number of rows, number of cols 
  //returns two dimensional array
}

function getRecords(sheet){
  var lastCol = sheet.getLastColumn(); // get the last col
  var lastRow = sheet.getLastRow(); // get the last row
  var records = sheet.getRange(2,1,lastRow-1,lastCol).getValues();

  // filter entries from empty values 
  var filtered = []
  for (let i = 0; i < records.length; i++) {
    filtered.push(records[i].filter(e => e != "skip")); // fill empty enteries with "skip"
  }
  return filtered;
}

function exportToFirestore(firestore, collectionName, properties, records) {
  var start = [4, 7, 11,13]
  var end = [6, 9, 12, 15]
  for (let i = 0; i < records.length; i++) {
    var data = {}
    var answerOptions = [];
    for (let j = 0; j < records[i].length; j++) {
      // curr key:val records[i][j]
      if (j < 4) {
        data[properties[j]] = records[i][j]
        Logger.log(properties[j], records[i][j])
      }
      else if (start.includes(j)){
        var currOption = {}
        currOption[properties[j]] = records[i][j]

      }
      else if (end.includes(j)){
        currOption[properties[j]] = records[i][j]
        answerOptions.push(currOption)
      }
      else{
        currOption[properties[j]] = records[i][j]
      }
    }
    data["answerOptions"] = answerOptions
    Logger.log(answerOptions)
    firestore.createDocument(collectionName, data);
  }

}