How to access Cloud Firestore from Google Sheets?

Question

I am trying to access Cloud Firestore database from Google sheet with the help of FirestoreGoogleAppsScript example, but as explained in the 5th point I have downloaded the file but not getting where to update the client_email, private_key and project_id in the project. So kindly guide me to move forward.

When you press "Create," your browser will download a .json file with your private key (private_key), service account email (client_email), and project ID (project_id). Copy these values into your Google Apps Script — you'll need them to authenticate with Firestore.

with a demo Code.gs

function myFunction() {
  projectid: "fir-79c39";
  key: "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCwMaJIlrvKjvgA\nVANhq81Oh0oTCRjyOMj6B4PE2wE9JhEnZQERkc0MX8k27DdFLQe3fnjNCc0+1Jj6\nB4Kf/86dtmXd/hTgDLsbUqF4q8vmjsBeJtdTEgcbWh0WhPKcHbAILAgFhcg0PtpT\nkI6oUs+U5cgh4OkaigIGBmt2epr3iZloj7HRw/Mj9L4Qb+0bTOmziqTUWWkPRUQ4\nmn+/Gnxh+S4XH7q4KedxbUZYPagoU2W+wktMZMVc6BMMTbFxNqG5vcUS6gv8oV1k\nJqrURiTqsV6rqJE1ig2X3KtMAwJ64LxhnD2KhPR5KMqkTr3sq5hS2bLch+r7+3Pe\n01DE4DcHAgMBAAECggEAC7uCXusKnjZe9Uai4wIZrbz8hb/5VUfKujt93sHmlzfN\ntJ3ZMF5RFR3bvDiGhai19tYJHT4l2KU3eo69eAIEtCPNLLcalMDERNF/xxVJgpTG\n4n4boIB20K9zeGhp71NawXCNKDD8X94/k4jwtuysvfF/HiJn40iFUfljdoQ7hXgb\noNvB/GE3xA/y86Vxv3zIln4pQdSvz2hiVVXciPRskhCo0fZ5li0uSYyutAxtw+4l\n7haoTqwAYpFlvuh9ggYkQYEhXzfpMZDXZDm1xY15R2wUoJ4TWd3jsxw+BdRRb/ic\nfMcoLdI7LRH+tcK0Z8GMaVSxxlj3jZpnAU7+xFl7wQKBgQDy4potBXWs8r7uK+5v\npJx2kvhX58PeH6V9v6VjLSwS7Oxy3tPaVK0AT4/oyH0C9UiXEi4LG09jEfozrTPi\nm/QVQka4ep4VlPtZCzP/3vqtRrE6ISTJxnF1qYegDW4RN18GIiCTajEsCoWEpjQm\n5l3qQS9UV/78LcxF7iopKFqbXwKBgQC5tSi9Vwdm970G0uJRS5mvPUWV5l6J/e0b\nUFoigXMh+n3liYaQkgU0o+tt8aa6S+t9eOwaVeeu3x8hShYHeFw55EKN3XE/SyMH\nGcNi9vtJXNKeSC+K5f1IU7QCcOTD9bfjdBCb/XVHatCSvI3oxakuuKtcOjm+yWBr\nb0C7ej+tWQKBgQC1nrjHHaQG9WXMV1FublsJV5EYupf+Sw/G3j3f0XF5nWyZ4QHt\n6w+KgfQMj47bXWswfEmtyHgujszXQ5bexttX4j1byWTYopTo2rXBiY1NcOUpoIsP\nzrIc1+4wwpohiZQm0/IkCW5Hjn6U2Gt7AxwLBT9NBJgJHn33dMZWDHTkZQKBgByO\nrSsgnPTKl/jaqQ7TT1jRVUgN2u/hanrOym2P9oJ9IKsRkO3eZoqHwoZvqLHxRs9O\nrxAFsdEEYqcd23FeouAMJ+fk66tOBJ7s6Uzoqg/IBGNuulDjuSVMKdxOO/+eY57I\nlF1IhqZq2JQYBsKK0s9qen9+PK8IDJm4y/2IeQ5RAoGAKdUKaAUaUeXwTSSofNBy\nRiB3vSZ683AEvgtUqQxj/jJBmlhhtus4JuSBnSOSb/pgnOy1ZRzFQjqRqhMeMgR9\nG9WtF3WV+QR7Hg3zoWManiL63tZA5TloPxEjlA9QOR+R0nkbdUT+1SrdII2mGvH0\nmPbmhAAu6XKP+E5QoV5wUDs=\n-----END PRIVATE KEY-----\n";
  email: "[e-mail]";

  var firestore = FirestoreApp.getFirestore(email, key, projectId);
}

error

ReferenceError: "email" is not defined. (line 7, file "Code")

Answer 1

You have to do as follows, for example in a simple function that fetches a collection:

function fecthFirstCollectionDocs() {

  var email = "[e-mail]";
  var key = "-----BEGIN PRIVATE KEY-----\your key here\n-----END PRIVATE KEY-----\n";
  var projectId = "zzzzzzzzz";

  var firestore = FirestoreApp.getFirestore(email, key, projectId);

  const allDocuments = firestore.query("FirstCollection").execute();
  Logger.log(JSON.parse(allDocuments));

}

The value of key is obtained by creating a service account, as explained in the Library documentation: https://github.com/grahamearley/FirestoreGoogleAppsScript#creating-a-service-account. It's quite easy, just follow the instructions.

You have to copy only the part between -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY-----\n from the .json file.

---

Edited following you comment of 9 April:

To solve the

ReferenceError: "email" is not defined. (line 7, file "Code") 

error you should correctly declare the variables you pass to the getFirestore() method, as shown in the code of my answer, and as follows:

instead of doing

  projectid: "fir-79c39";

you should do

  var projectid = "fir-79c39";

You declare a variable named projectid that you use in the getFirestore() method. The same for key and email.

Answer 2

function writeDataToFirestore() {
  var projectId = "XXXXXXXX";
  var key = "-----BEGIN PRIVATE KEY-----\nXXXXXXXXXX\n-----END PRIVATE KEY-----\n";
  var email = "[e-mail]";
  var firestore = FirestoreApp.getFirestore(email, key, projectId);
  const data = {
    "name": "test!"
  }
  firestore.createDocument("FirstCollection", data)
}