Gettting wrong .JSON format using GApps Script to sync GSheets to Firebase Realtime-CodePudding

how are you doing?

Everything is running fine and I'm able to sync my Google Sheets to Firebase using a script (below) on Google Apps Script, but the format of the JSON is not what I'm expecting. Is there a way to get exactly the way I need? See the images, I would like to keep the same format as it's on Google Sheets. Thanks!

Goolge Sheets - Wrong JSON - Correct JSON

var secret = 'XXXXXXXXXXX'

function getFirebaseUrl(jsonPath) {
  /*
  We then make a URL builder
  This takes in a path, and
  returns a URL that updates the data in that path
  */
  return (
    'https://XXXXXXXXXXXXXXXXX.com/'  
    jsonPath  
    '.json?auth='  
    secret
  )
}

function syncMasterSheet(excelData) {
  /*
  We make a PUT (update) request,
  and send a JSON payload
  More info on the REST API here : https://firebase.google.com/docs/database/rest/start
  */
  var options = {
    method: 'put',
    contentType: 'application/json',
    payload: JSON.stringify(excelData)
  }
  var fireBaseUrl = getFirebaseUrl('moedas')

  /*
  We use the UrlFetchApp google scripts module
  More info on this here : https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
  */
  UrlFetchApp.fetch(fireBaseUrl, options)
}

function startSync() {
  //Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSheet()
  //Get the number of rows and columns which contain some content
  var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()]
  //Get the data contained in those rows and columns as a 2 dimensional array
  var data = sheet.getRange(1, 1, rows, columns).getValues();
var dataObject = {};
//Loop through the rows creating a new object for each one
  for(var i=1; i < data.length; i  ) {
    var dataRow = data[i];
    var moedaName = dataRow[0];
    var idName = dataRow[1];
    var currentDate = dataRow[2];
    var currentValue = dataRow[3];
    var dailyVar = dataRow[4];
    var moedaImg = dataRow[5];
    // we then create our first property on our data object dataObject.code-name : { }
    dataObject[idName] = {
      moedaName:moedaName,
      idName:idName,
      currentDate:currentDate,
      currentValue:currentValue,
      dailyVar:dailyVar,
      moedaImg:moedaImg,
    }}

  syncMasterSheet(dataObject)
}

I've tried the script attached. I'm expecting to get the JSON with the format as the Google Sheets.

CodePudding user response：

When you use range.getValues() you get the values as stored by Google, e.g. your formatting such as decimal precision and date is not applied. There is another function to get the values as displayed instead: range.getDisplayValues(), docs: https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues

Change your code to this:

  var data = sheet.getRange(1, 1, rows, columns).getDisplayValues();