firebase - Firestore Import Google Sheets Array

Question

How can I get array data formatted into google sheets so I can import it into firestore? See imagee below. I have a script to import data from Google sheets directly into my Firestore but I don't know how to format an array with string items like the image below. What is the best way to do this in Google sheets/data format?

// Create a menu item for 'Export to Firestore' 
function onOpen() {
  SpreadsheetApp.getUi().createMenu('?? Firebase').addItem('Export to Firestore', 'main').addToUi();
}

function main() {
  // Get the active spreadsheet and its name for the collection name
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetName = sheet.getName();

 // Get the first row as object properties
 // [ 'comment', 'companyId', 'date', 'email', 'score', 'userId']
 var properties = getProperties(sheet);
  
 // Get the next 100 rows as records
 // [ ['test comment', 'ec24fmJeLA93l5jWPAN0', '1602547200.00', 'bdavis@wftst.com', '3', 'a8waViyRAJWGr2h6Gc3u'] ] 
var records = getRecords(sheet);
  
 // Export to Firestore private key removed for security reasons
  var firestore = FirestoreApp.getFirestore('XXXXXX', 'XXXXXXX', 'XXXXXX'); 

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

function exportToFirestore(firestore, collectionName, properties, records) {
  records.map(function(record) {
    // record: ['test comment', 'ec24fmJeLA93l5jWPAN0', '1602547200.00', 'bdavis@wftst.com', '3', 'a8waViyRAJWGr2h6Gc3u']
    // props : [ 'comment', 'companyId', 'date', 'email', 'score', 'userId']
    var data = {};
    properties.forEach(function(prop,i) { data[prop] = record[i]; });
    return data; 
  }).forEach(function(data) {
    firestore.createDocument(collectionName, data);
  });
}

function getProperties(sheet) {
  return sheet.getRange(1, 1, 1, 6).getValues()[0]; // [ [ 'comment', 'companyId', 'date', 'email', 'score', 'userId'] ]
}

function getRecords(sheet) {
 return sheet.getRange(2, 1, 1, 6).getValues(); 
}

question from:https://stackoverflow.com/questions/65649973/firestore-import-google-sheets-array

Answer 1

I believe your goal as follows.

• You have a sample Spreadsheet like below.

  

• You want to retrieve the values and put them to Firestore as follows.

  

• You want to achieve this using Google Apps Script.

Modification points:

• In this case, I would like to propose the following flow.
  1. Retrieve values from Spreadsheet.
  2. Create an object for sending to Firestore.
  3. Send the object to Firestore.

When above flow is reflected to the sample script, it becomes as follows.

Modified script:

Before you use this script, please set the variables of spreadsheetId, sheetName and email, key, projectId of FirestoreApp.getFirestore(email, key, projectId), and also, collectionName.

const spreadsheetId = "###"; // Please set the Spreadsheet ID.
const sheetName = "Sheet1"; // Please set the sheet name.

// 1. Retrieve values from Spreadsheet.
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
const [header, ...data] = sheet.getDataRange().getValues();

// 2. Create an object for sending to Firestore.
const obj = data.reduce((ar, r) => {
  const temp = {};
  r.forEach((c, j) => {
    let key = header[j];
    if (key.includes("Tags")) {
      key = "tags";
      temp[key] = temp[key] ? temp[key].concat(c) : [c];
    } else {
      temp[key] = c;
    }
  });
  ar.push(temp);
  return ar;
}, []);

// 3. Send the object to Firestore.
const firestore = FirestoreApp.getFirestore(email, key, projectId);
firestore.createDocument(collectionName, obj);

Result:

When this script is run, the following situation can be obtained.

From:

To

Note:

• In this sample script, the sample Spreadsheet I show you above from your updated question is used. So, when your actual situation is different from it, the script might not be able to be used. Please be careful this.
• In your sample Spreadsheet, I understood that the headers of tags are Tags 0, Tags 1 and Tags 2. This script is for the headers. Please be careful this. If your actual situation is not correct, please modify the script.
• In this sample script, it supposes that you have already been able to get and put values to Firestore. Please be careful this.
• Please use this script with enabling V8 runtime.

References:

• reduce()
• includes()

Added:

About the following your request in your replying,

1. I want each row in the spreadsheet to be its own unique document within a collection. 2) Tags are the only sets of data that need to be in array on the document. The rest of them need to be just strings

How about modifying above script as follows?

From:

// 3. Send the object to Firestore.
const firestore = FirestoreApp.getFirestore(email, key, projectId);
firestore.createDocument(collectionName, obj);

To:

// 3. Send the object to Firestore.
const firestore = FirestoreApp.getFirestore(email, key, projectId);
obj.forEach(r => firestore.createDocument(collectionName, r));

• In your current script, collectionName is used? Or scores is used? Unfortunately, from your replying, I couldn't understand about this. So I just modified my proposed script. So please modify the variable name for your actual situation.