With Google AppScripts, Exporting data from firebase to google sheet's data have data type with the value

user_12503959 0
Viewed 297

I'm using Google App Scripts and Firestore GoogleAppScripts for data fetching from firestore to google sheet as explained on this library.

Here's my code.

function getGemExchange() {
  // 1. Get a Firestore instance
  const firestore = getFirestore()

  // 2. Get a collection from Firestore
  const userDocuments = firestore.getDocuments('gemExchangeRequests').map(document => document.fields)

  // 3. Get the first document from the collection 
  const first = userDocuments[0];
  const columns = Object.keys(first);

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(columns);

  // 4. Turn each document into an array to be appended to the sheet
  userDocuments.forEach(document => {
    const row = columns.map(column => document[column])
    sheet.appendRow(row)
  })
}

It all works fine except the values have its data type with it.

status  taxAmount   bankDetails gemAmount   uniqueId    serviceFee
{stringValue=PENDING}   {doubleValue=0.02}  {stringValue=U2FsdGVkX1/a2sX4CaKi0Ham/dSSEzlg7QNca236+clZbRDwHZwe8C7Xhy53ybk0c6l34CLYtQ+0KFnDOuAAfUc4WUKRfQTPWxS8NDHP2kREeRzX2BJGZ6JpL3NfxmsbR/dGpvKqy/Pa+Of3guJmjg==}  {integerValue=33}   {stringValue=APR-TC0}   {integerValue=2}

So I want to get rid of data types such as "stringValue", "integerValue" or "doubleValue" on my Google Spreadsheet.

For example, I want "PENDING" for the "status" column, not "{stringValue=PENDING}"

What should I do in this case?

google-cloud-firestore firebase google-apps-script
1 Answers

In your situation, how about the following modification?

From:

userDocuments.forEach(document => {
  const row = columns.map(column => document[column])
  sheet.appendRow(row)
})

To:

const values = userDocuments.map(document => columns.flatMap(column => Object.values(document[column])));
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  • In this modification, each value is retrieved from document[column]. And, the values are put using setValues instead of appendRow. By this, the process cost will be reduces a little. Ref

Note:

  • I thought that in your script, the arrange of the header row might be changed every run. So, when you want to constant the header row, I would like to propose to declare the header row as a variable in the script.

References:

user_71086530
Related