Uploading Multiple Records Via Salesforce API Using Google Apps Script

Photo by airfocus on Unsplash

Sometimes, when using Salesforce, we come across the situation of needing to upload several object records at once, such as accounts, contacts, tasks, etc.

There are several ways to resolve this issue. One approach that can be efficient is through Google Sheets, especially if you want to “convert” each row in a table into separate records in Salesforce.

Example of a table with records of the task object.

In the example of this article, I will talk about an implementation for bulk exporting of tasks using information contained in a table in Google Sheets using the Salesforce API. This implementation can also be replicated to other members of a work team and can also be used for other sObjects.

Generating the access token

Foremost, we need to gather the necessary information to accredit ourselves and generate a valid access token. For this, we need the following information:

endpoint = 
grant_type = "password"
client_id =  
client_secret =  
username = "yourLoginEmail@domain.com"
password = "yourPassword1234"
security_token =

As can be seen, some information is already at our disposal, such as grant_type, user and password, while the others must be generated.

As for the endpoint, it can vary depending on the environment you are accessing:

• Production (most common): "https://login.salesforce.com/services/oauth2/token"
• Sandbox: "https://test.salesforce.com/services/oauth2/token"
• If your organization has a custom domain enabled: "https://yourcustomdomain.my.salesforce.com/services/oauth2/token"

Now, it is necessary to create a Connected App, which can be done within the Salesforce platform. The purpose of creation is to get the client_id and client_secret. The article below shows how to create it:

How to Create a Salesforce Connected App - Softbuilder Blog

After that, the last information should still be the Salesforce security token, which, if you don’t have it or don’t know it, you can renew it through Salesforce by going to Settings –> My Personal Information –> Reset My Security Token. That way, an email will be sent automatically with information. Remembering that, after this process, if there is a previous security token that is being used in another application, it will no longer be valid.

With all the information available, it is now possible to make a request to generate the access token that will be used to upload the records to Salesforce.

This request can be made in several ways, but as this is an implementation made in Google Sheets, the entire process will be through Google Apps Script, as seen below:

The response of this function should look something like this:

{
  "access_token":"00DR00000008oBT!AQwAQCPqzc_HBE59c80QmEJD ... ",
  "instance_url":"https://MyDomainName.my.salesforce.com",
  "id":"https://login.salesforce.com/id/00DR00000008oBTMAY ... ",
  "token_type": "Bearer",
  "issued_at": "1513887500425",
  "signature": "3PiFUIioqKkHpHxUiCCDzpvSiM2F6//w2/CslNTuf+o="
}

Security

Now, with the access token, we can make requests to send and receive data from Salesforce, but there are some points of attention:

• The generated token may have an expiration date, so at other times, the same token may no longer be valid, so it is necessary to keep this function in the worksheet so that an updated token is always obtained.
• As the token was generated based on the individual information of a given user (login, password and security token), the creator of all records will be this same user, regardless of who has actually uploaded them.
• In addition, although in individual use there is not so much problem, the fact that the individual access information of some user is explicit in a document can be a critical point if a team has access to it, thus, it is necessary to have some way to hide such information.

There are a few ways to avoid the last problem, and, in the context of Google Sheets + Apps Script, one solution found is for each user to save their own credentials in a document in their Google Docs account where only they have access, so, each would make a copy of the spreadsheet and “authenticate” itself to the spreadsheet by entering the ID of that document in some requested cell. The content of this document would be a JSON with the following structure:

{
"username":"yourLoginEmail@domain.com",
"password":"yourPassword1234",
"securityToken":"yourSecurityToken"
}

There may be other solutions, but this one already allows that, even if a User B has access to the copy of the spreadsheet with the ID of User A’s credentials, he will not be able to access User A’s personal information because he does not have access to the referenced document and the execution of the function would be interrupted, because for the function to execute correctly it is precisely this permission that only User A has.

In addition, this idea also solves the problem of the creator of each record, as each one, at each execution, would generate an updated token based on their private credentials.

Thus, to hide the individual information, the previous function could be rewritten as below:

Creating the Request Body

Before exporting records in bulk, it is necessary to define what will be exported. In addition, this information must be in a format that Salesforce recognizes, so it is necessary to transform the spreadsheet into a JSON that will be inserted in the request.

To insert multiple records to Salesforce, the JSON must have the following structure:

{
  "records": [
    {
      "attributes":{
        "type":"Task",
        "referenceId":"ref1"
      },
      "Subject":"Task 1",
      "ActivityDate":"2022-09-21",
      "OwnerId":"0A55f00nWiPJzvRl2T",
      "WhatId":"0B15f00YH4A7FmqHX7",
      "WhoId":"0C35f00KIhHfH9LieC",
      "Status":"Completed",
      "Description":"This is the description of Task 1 ... "
    },
 
    ...
    
    {
      "attributes":{
        "type":"Task",
        "referenceId":"ref8"
      },
      "Subject":"Task 8",
      "ActivityDate":"2022-09-21",
      "OwnerId":"0A55f00nWiPJzvRl2T",
      "WhatId":"0B15f00lFVNcU69tbj",
      "WhoId":"",
      "Status":"Completed",
      "Description":"This is the description of Task 8... "
    }
  ]
}

In short, there is only one property on this object, called records. This property contains an array of objects, where each one represents a record, which in our example are the table rows.

Note that in attributes, there is a property called type, referring to the sObject that will be exported, which in this case is "Task", but could also be, for example, "Account", "Contact", "User", etc., depending on the type of information the table contains.

Another important point is that all the columns of the table must be named exactly like the fields belonging to the object that will be exported.

With this, the function below transforms the spreadsheet presented at the beginning of the article into a JSON with the same structure:

Exporting Records to Salesforce

Finally, in order to export multiple records, we need to make a “post” request with the URL below:

{instance url}/services/data/{API version}/composite/tree/{sObject}

With this URL, in the request headers we insert the access token and in the request body we insert all the records to be exported in JSON format, as written earlier:

If the export was successful, with all the correct data, both in the request and in the spreadsheet, the response should look like this:

{
  hasErrors: false,
  results: [
    { referenceId: 'ref1', id: '0BT4f00TtjURwAf45M' },
    { referenceId: 'ref2', id: '0BT4f00TtjURwBf45M' },
    { referenceId: 'ref3', id: '0BT4f00TtjURwCf45M' },
    { referenceId: 'ref4', id: '0BT4f00TtjURwDf45M' },
    { referenceId: 'ref5', id: '0BT4f00TtjURwEf45M' },
    { referenceId: 'ref6', id: '0BT5f00TtjURwFf45M' },
    { referenceId: 'ref7', id: '0BT4f00TtjURwGf45M' },
    { referenceId: 'ref8', id: '0BT4f00TtjURwHf45M' },
  ]
}

Final Considerations

In this article, the process of uploading information to Salesforce using its API in Google Sheets was shown, however, in addition, several other things can also be implemented together to make the use of the spreadsheet even more user-friendly, such as:

• Create a button that, when clicked, automatically executes all the processes mentioned so far.
• Create a name-id relationship for all ID fields, so that, when filling in the information in Google Sheets, it is possible to fill in such fields by the names in an auxiliary table, while the respective IDs would be inserted in the table that would be used in the request.
• Create a function that visually indicates whether a record has exported or not.

Thanks for reading, until next time!