A Google Apps Script, which simply connects and fetches data from Salesforce RESTful API with OAuth authentication.

SimpleSalesforceConnection.js

/**
 * Connect and fetch Salesforce data via OAuth
 */
function queryDataFromSalesforce() {
  // Read OAuth consumer key / secret of this client app from script properties, 
  // which can be issued from Salesforce's remote access setting in advance.
  var sfConsumerKey = ScriptProperties.getProperty("sfConsumerKey");
  var sfConsumerSecret = ScriptProperties.getProperty("sfConsumerSecret");
  if (!sfConsumerKey || !sfConsumerSecret) {
    Browser.msgBox("Register Salesforce OAuth Consumer Key and Secret in Script Properties");
    return;
  }

  // Register new OAuth service, named "salesforce"
  // For OAuth endpoint information, see help doc in Salesforce.
  // https://na7.salesforce.com/help/doc/en/remoteaccess_oauth_1_flows.htm
  var oauth = UrlFetchApp.addOAuthService("salesforce");
  oauth.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler");
  oauth.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler");
  oauth.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(sfConsumerKey));
  oauth.setConsumerKey(sfConsumerKey);
  oauth.setConsumerSecret(sfConsumerSecret);

  // Convert OAuth1 access token to Salesforce sessionId (mostly equivalent to OAuth2 access token)
  var sessionLoginUrl = "https://login.salesforce.com/services/OAuth/u/21.0";
  var options = { method : "POST", oAuthServiceName : "salesforce", oAuthUseToken : "always" };
  var result = UrlFetchApp.fetch(sessionLoginUrl, options);
  var txt = result.getContentText();
  var accessToken = txt.match(/<sessionId>([^<]+)/)[1];
  var serverUrl = txt.match(/<serverUrl>([^<]+)/)[1];
  var instanceUrl = serverUrl.match(/^https?:\/\/[^\/]+/)[0];
  
  // Query account data from Salesforce, using REST API with OAuth2 access token.
  var fields = "Id,Name,Type,BillingState,BillingCity,BillingStreet";
  var soql = "SELECT "+fields+" FROM Account LIMIT 100";
  var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
  var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } });
  var queryResult = Utilities.jsonParse(response.getContentText());

  // Render query result to Spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.setFrozenRows(1);

  // Render all field names in header row.
  var cell = sheet.getRange('a1');
  fields = fields.split(',');
  fields.forEach(function(field, j){ cell.offset(0, j).setValue(field) })

  // Render result records into cells
  queryResult.records.forEach(function(record, i) {
    fields.forEach(function(field, j) { cell.offset(i+1, j).setValue(record[field]) });
  });

}

@stomita this code no longer works due to deprecation of google client apis. Please kindly revise this if possible.

Hi had a go at porting this script. However I'm not getting a valid token exchange when hitting the authorisation url. Salesforce is able to see my call back page.

But the access token being returned is different. Throwing this error : Error handling callback: token mismatch (line 239, file "Service", project "OAuth1")

function getSalesforceService() {
  var sfConsumerKey = ScriptProperties.getProperty("sfConsumerKey");
  var sfConsumerSecret = ScriptProperties.getProperty("sfConsumerSecret");
  if (!sfConsumerKey || !sfConsumerSecret) {
    Logger.log("Register Salesforce OAuth Consumer Key and Secret in Script Properties");
    return;
  }
  // Register new OAuth service, named "salesforce"
  var oauth = OAuth1.createService("salesforce");
  //oauth.
  oauth.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler");
  oauth.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler");
  oauth.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(sfConsumerKey));
  oauth.setConsumerKey(sfConsumerKey);
  oauth.setConsumerSecret(sfConsumerSecret);
  oauth.setCallbackFunction("authCallback");

  oauth.setMethod("POST");
  oauth.setPropertyStore(PropertiesService.getScriptProperties());
  var authorizationUrl = oauth.authorize();


  return oauth
}

function authCallback(request) {
Logger.log(request);
  var service = getSalesforceService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this page.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this page');
  }
}

I then use

  var oauth = getSalesforceService();   
  if (oauth.hasAccess()) { ....
  } else {
    var authorizationUrl = oauth.authorize();
    Logger.log('Please visit the following URL and then re-run the script: ' + authorizationUrl);
  }

to call the service.

Ultimately the token being written to the script properties in the oauth1.salesforce value pair is different to the one I log in the callback function.

Has anyone been able to port sfdc oauth1 to the new google libraries?

quick update here. If this is still a pending issue for anyone. After wrestling with oAuth1 for a while, I migrated my script to oAuth2 using this library https://github.com/googlesamples/apps-script-oauth2 (project key MswhXl8fVhTFUH_Q3UOJbXvxhMjh3Sh48)

function getSalesforceService() {
  var sfConsumerKey = ScriptProperties.getProperty("sfConsumerKey");
  var sfConsumerSecret = ScriptProperties.getProperty("sfConsumerSecret");
  if (!sfConsumerKey || !sfConsumerSecret) {
    Logger.log("Register Salesforce OAuth Consumer Key and Secret in Script Properties");
    return;
  }
  return OAuth2.createService('salesforce')
      // Set the endpoint URLs, which are the same for all sfdc services.
      .setAuthorizationBaseUrl('https://login.salesforce.com/services/oauth2/authorize')
      .setTokenUrl('https://login.salesforce.com/services/oauth2/token')
      .setClientId(sfConsumerKey)
      .setClientSecret(sfConsumerSecret)
      // Set the name of the callback function in the script referenced
      // above that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')
      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

}

function authCallback(request) {
  var service = getSalesforceService();
  var isAuthorized = service.handleCallback(request);
  if (isAuthorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

In my script I then used

var oauth = getSalesforceService();     
  if (oauth.hasAccess()) { 
        var instanceUrl = "https://xxx.salesforce.com";
        var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql);
         var options =  {
          headers: {
            Authorization: 'Bearer ' + oauth.getAccessToken()
          }
        } 
        var response = UrlFetchApp.fetch(queryUrl,options); 
        var queryResult = Utilities.jsonParse(response.getContentText()); 

Only hack was hard coding the instance URL. That used to be sent back in the SOAP in @stomita's code.

Hope this was useful for someone.

Hi, I would like to know how the script can be changed to manage a SOAL query with child to parent or parent to child query construct

I am trying to access the SalesforceAPI from Google App script and I have used OAuth protocol for authorization. I was able to pull the salesforce information in the application successfully for the first time. However, on the next day I got the following error.
[ { "message": "Session expired or invalid", "errorCode": "INVALID_SESSION_ID" } ]
When I got the error first time. I have created new appscript and copy pasted the code from previous app script as it is and it worked for one day without any error again next day onwards I am getting the same error.
If there were any problem with access token or instance url then it would not work in the first place itself. I think problem is not with instance url or access token.

How can I fix this issue?

//Request:
function getService() {
return OAuth2.createService('Saleforce')
// Set the endpoint URLs.
.setAuthorizationBaseUrl('https://login.salesforce.com/services/oauth2/authorize')
.setTokenUrl('https://login.salesforce.com/services/oauth2/token')
// Set the client ID and secret.
.setClientId(CLIENT_ID)
.setClientSecret(CLIENT_SECRET)
// Set the name of the callback function that should be invoked to
// complete the OAuth flow.
.setCallbackFunction('authCallback')
// Requests offline access.
.setParam('access_type', 'offline')
// Set the property store where authorized tokens should be persisted.
.setPropertyStore(PropertiesService.getUserProperties());
}

//response:
var url1 = service.getToken().instance_url +parameter1;
var headers={
Authorization: 'Bearer ' + service.getAccessToken()
};
var options = {muteHttpExceptions: true,"headers":headers};
var response1 = UrlFetchApp.fetch(url1,options);
Logger.log(response1);

Output:
[{"message":"Session expired or invalid","errorCode":"INVALID_SESSION_ID"}]

Did you ever solve this problem? I'm having the exact same issue.