Using Google script to list page layouts for each field returned

I have a Google script I use to gather the following items from an object:
Field Label
API Name
Data Type
Length
Decimal Place
Required
Unique
External ID
Default Value
Picklist Values
Formula
ReferenceTo
IsCustom
Createable
Updateable
HelpText
Encrypted
I want to add a column to list the page layouts a field is included on. I've tried to find an example on how to do it but no luck. My thinking is I'd like it listed like how pick list values are listed. I can see how to add a column - I did this already with Encrypted - but to have it pull all the page layouts the field is used on - I'm lost.
An example on output would be this:
example of spreadsheet with page layout column
example of spreadsheet with page layout column
Here is the Google Scrip I now use:
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu('Salesforce', [
    {name: 'Login', functionName: 'sfdcLoginApp'},
    {name: 'Fields List', functionName: 'sfdcGetFieldsApp'}
  ]);
}

//--------------------------------------------------
// Common UI Application
//--------------------------------------------------
function appCancel_(event) {
  return UiApp.getActiveApplication().close();
}

//--------------------------------------------------
// SFDC Login
//--------------------------------------------------
function sfdcLoginApp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication();
  app.setTitle('Salesforce Login');
  app.setHeight(200);
  
  var createUi = function(name, options, styles, children) {
    return createUi_(app, name, options, styles, children);
  };
  var setHandler = function(target, callbackElement, type, handlerName) {
    return setHandler_(app, target, callbackElement, type, handlerName);
  }
  
  sforce.connection.loadProperties();
  var instance = sforce.connection.INSTANCE || '';
  
  var layout = createUi('VerticalPanel', null, {margin:'auto', paddingTop:'30px'});
  app.add(layout);
  layout.add(
    createUi(
      'HorizontalPanel',
      null,
      {margin:'auto'},
      [
        ['Label', {text:'Instance(e.g. na7)'}, {marginRight:'20px'}],
        ['TextBox', {name:'instance', text:instance}, {width:'100px', marginBottom:'10px'}]
      ]
    )
  );
  layout.add(
    createUi(
      'HorizontalPanel',
      null,
      {margin:'auto'},
      [
        ['Label', {text:'User Name　'}, {marginRight:'20px'}],
        ['TextBox', {name:'username'}, {width:'300px', marginBottom:'10px'}]
      ]
    )
  );
  layout.add(
    createUi(
      'HorizontalPanel',
      null,
      {margin:'auto'},
      [
        ['Label', {text:'Password (with token)　'}, {marginRight:'20px'}],
        ['PasswordTextBox', {name:'password'}, {width:'300px', marginBottom:'10px'}]
      ]
    )
  );
  layout.add(
    createUi(
      'HorizontalPanel',
      null,
      {margin:'auto'},
      [
        setHandler(
          createUi('Button', {text:'OK'}),
          layout,
          'Click',
          'sfdcLoginOk_'
        ),
        setHandler(
          createUi('Button', {text:'Cancel'}),
          layout,
          'Click',
          'appCancel_'
        )
      ]
    )
  );
   
  ss.show(app);
}

function sfdcLoginOk_(event) {
  var instance = event.parameter.instance;
  var username = event.parameter.username;
  var password = event.parameter.password;
  
  if (!username || !password) {
    throw new Error('Invalid Username or Password');
  }
  
  sforce.connection.login(instance, username, password);
  
  Browser.msgBox('Login Successfully. Next: Choose "Fields List" menu.');
  
  return UiApp.getActiveApplication().close();
}

//--------------------------------------------------
// SFDC GetFields
//--------------------------------------------------
function sfdcGetFieldsApp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var gd = sforce.connection.describeGlobal();
  ss.show(createGetFieldsUi_(gd));
}

function createGetFieldsUi_(globalDescribe) {
  var app = UiApp.createApplication();
  app.setTitle('Get Salesforce Objects');
  app.setHeight(150);
  app.setWidth(850);  //HRW.r1 (added line)
  
  var createUi = function(name, options, styles, children) {
    return createUi_(app, name, options, styles, children);
  };
  var setHandler = function(target, callbackElement, type, handlerName) {
    return setHandler_(app, target, callbackElement, type, handlerName);
  }
  
  var sobjects = globalDescribe.sobjects;
  var listBoxItems = [];
  for (var i = 0, len = sobjects.length; i < len; i++) {
    var so = sobjects[i];
    var lbl = so.label + ' (' + so.name + ')';  //HRW.r1 (added)
    //listBoxItems.push([so.label, so.name]);   //HRW.r1 (commented out)
    listBoxItems.push([lbl, so.name]);          //HRW.r1 (added)
  }
  listBoxItems.sort();                          //HRW.r1 (added)
  
  var panel = createUi('VerticalPanel', null, {margin:'20px auto'})
  panel.add(createUi('ListBox', {name:'sobject', add:{item:listBoxItems}}));
  panel.add(createUi('HorizontalPanel', null, {margin:'20px auto'},
    [
      setHandler(createUi('Button', {text:'OK'}), panel, 'Click', 'sfdcGetFieldsOk_'),
      setHandler(createUi('Button', {text:'Cancel'}), panel, 'Click', 'appCancel_')
    ]
  ));
  
  app.add(panel);
  return app;
}

function sfdcGetFieldsOk_(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.getActiveApplication();
  var sobject = event.parameter.sobject;
  var desc = sforce.connection.describeSObject(sobject);
  
  
  var fields = desc.fields;
  var values = [
    [
      newFieldHeader_('Field Label'),
      newFieldHeader_('API Name'),
      newFieldHeader_('Data Type'),
      newFieldHeader_('Length'),
      newFieldHeader_('Decimal Place'),
      newFieldHeader_('Required'),
      newFieldHeader_('Unique'),
      newFieldHeader_('External ID'),
      newFieldHeader_('Default Value'),
      newFieldHeader_('Picklist Values'),
      newFieldHeader_('Formula')
      // Herb Added 2014-12-04
      , newFieldHeader_('ReferenceTo')
      , newFieldHeader_('IsCustom')
      , newFieldHeader_('Createable')
      , newFieldHeader_('Updateable')
      , newFieldHeader_('HelpText')    
      // Martin Added 2018-05-17
      , newFieldHeader_('Encrypted')  
      //-------------------------------


    ]
  ];
  for (var i = 0, len = fields.length; i < len ; i++) {
    var field = fields[i];
    Logger.log(field);
    values.push(newFieldRow_(field));
  }
  
  var sheetName = desc.label;
  var sheet = getOrCrreateSheet_(sheetName);
  sheet.clear();
  var writer = new SheetValuesWriter_(sheet);
  writer.writeValues(values);
  
  return UiApp.getActiveApplication().close();
}
function newFieldHeader_(value) {
  return {
    value: value,
    fontWeight: 'bold',
    horizontalAlignment: 'center',
    backgroundColor: '#CCC',
    border: [true, true, true, true, false, false]
  };
}
function newFieldRow_(field) {
  var picklists = field.picklistValues;
  var pickVals = [];
  for (var i = 0, len = picklists.length; i < len; i++) {
    var item = picklists[i];
    Logger.log(item);
    pickVals.push( (item.defaultValue?' * ':'   ') + item.label);
  }
  return [
    field.label,
    field.name,
    field.type,
    field.precision ? field.precision.toString() : field.length.toString(),
    field.scale.toString(),
    (! field.nillable && ! field.defaultedOnCreate && (field.createable || field.updateable || field.encrypted) ? 'X' : ''),
    booleanCellValue_(field.unique),
    booleanCellValue_(field.externalId),
    field.defaultValue || field.defaultValueFormula,
    pickVals.join('\n'),
    field.calculatedFormula || (field.autoNumber ? '(Auto-Number)' : '')
    // Herb Added 2014-12-04
    , (field.referenceTo ? field.referenceTo.toString() : '')
    , booleanCellValue_(field.custom)
    , booleanCellValue_(field.createable)
    , booleanCellValue_(field.updateable)
  
    , (field.inlineHelpText ? field.inlineHelpText : '')
        //Martin Added 2018-05-17
    , booleanCellValue_(field.encrypted) 
  ];
}
function booleanCellValue_(v) {
    return v ? 'Y' : '';
}

//--------------------------------------------------
// Properties
//--------------------------------------------------
function getScriptProperties_(names, prefix) {
  var props = {};
  prefix = prefix || '';
  for (var i = 0, len = names.length; i < len; i++) {
    var n = names[i];
    props[n] = ScriptProperties.getProperty(prefix + n);
  }
  return props;
}

//--------------------------------------------------
// Connection
//--------------------------------------------------
var sforce = {};
sforce.Connection = function() {};
//HRW r.2 (updated API version was 20.0 now 23.0)
//HRW r.3 (updated API version was 23.0 now 24.0)
//HRW r.4 (updated API version was 24.0 now 28.0)
//HRW r.5 (updated API version was 28.0 now 29.0)
//HRW r.6 (updated API version was 29.0 now 31.0)
//HRW r.7 (updated API version was 31.0 now 33.0)
sforce.Connection.prototype = {
  apiVersion: '33.0',
  propertyNames: ['SESSTIONID','INSTANCE'],
  propertyPrefix: 'SFDC_',
  loadProperties: function() {
    var props = getScriptProperties_(this.propertyNames, this.propertyPrefix);
    for (var i in props) {
      this[i] = props[i];
    }
  },
  login: function(instance, username, password) {
    var xml = '\
<se:Envelope xmlns:se="http://schemas.xmlsoap.org/soap/envelope/">\
    <se:Header xmlns:sfns="urn:partner.soap.sforce.com">\
    </se:Header>\
    <se:Body>\
        <login xmlns="urn:partner.soap.sforce.com" xmlns:ns1="sobject.partner.soap.sforce.com">\
            <username>' + username + '</username><password>' + password + '</password>\
        </login>\
    </se:Body>\
</se:Envelope>';
    var url = 'https://' + instance + '.salesforce.com/services/Soap/u/' + this.apiVersion;
    var res = UrlFetchApp.fetch(url, {
      headers: {
        SOAPAction: '""'
      },
      contentType: 'text/xml',
      method: 'POST',
      payload: xml
    });
    var root = Xml.parse(res.getContentText()).getElement();
    var sessionId = root.getElement('http://schemas.xmlsoap.org/soap/envelope/', 'Body')
        .getElement('loginResponse')
        .getElement('result')
        .getElement('sessionId')
        .getText();
    ScriptProperties.setProperty('SFDC_INSTANCE', instance);
    ScriptProperties.setProperty('SFDC_SESSTIONID', sessionId);
  },
  describeGlobal: function() {
    this.loadProperties();
    var url = 'https://' + this.INSTANCE + '.salesforce.com/services/data/v' + this.apiVersion + '/sobjects';
    var response = sendGetRequest_(url, this.SESSTIONID);
    return Utilities.jsonParse(response.getContentText());
  },
  describeSObject: function(name) {
    this.loadProperties();
    var url = 'https://' + this.INSTANCE + '.salesforce.com/services/data/v' + this.apiVersion + '/sobjects/' + name + '/describe';
    var response = sendGetRequest_(url, this.SESSTIONID);
    return Utilities.jsonParse(response.getContentText());
  }
};
sforce.connection = new sforce.Connection();
sforce.connection.loadProperties();

function sendGetRequest_(url, accessToken) {
  var res = UrlFetchApp.fetch(url, {
    headers: {
      Accept: 'application/json',
      Authorization: 'OAuth ' + accessToken,
      "X-PrettyPrint": true
    },
    contentType: 'application/json',
    method: 'GET'
  });
  return res;
}



//--------------------------------------------------
// UI
//--------------------------------------------------
function createUi_(app, name, options, styles, children) {
  var ui = app['create' + name]();
  if (isArray_(options)) {
    return addChildUi_(app, ui, children = options);
  }
  if (options != null && 'add' in options) {
    var addOptions = options.add;
    for (var i in addOptions) {
      var fnName = 'add' + toUpperCaseFirstChar_(i);
      var values = addOptions[i];
      for (var j = 0, len = values.length; j < len; j++) {
        var v = values[j];
        //ui[fnName].apply(ui, v); //TypeError: Cannot find default value for object.
        if (isArray_(v)) {
          ui[fnName](v[0], v[1]);
        } else {
          ui[fnName](v);
        }
      }
    }
  }
  if (options != null && 'set' in options) {
    options = options.set;
  }
  for (var i in options) {
    if (i !== 'add') {
      var op = toUpperCaseFirstChar_(i);
      ui['set' + op](options[i]);
    }
  }
  if (isArray_(styles)) {
    return addChildUi_(app, ui, children = styles);
  }
  for (var i in styles) {
    ui.setStyleAttribute(i, styles[i]);
  }
  if (children) {
    return addChildUi_(app, ui, children);
  }
  return ui;
}

function addChildUi_(app, parent, children) {
  for (var i = 0, len = children.length; i < len; i++) {
    var c = children[i];
    if (isArray_(c)) {
      parent.add(createUi_(app, c[0], c[1], c[2], c[3]));
    } else {
      parent.add(c);
    }
  }
  return parent;
}
 
function setHandler_(app, target, callbackElement, type, handlerName) {
  var handler = app['createServer' + type + 'Handler'](handlerName);
  handler.addCallbackElement(callbackElement);
  target['add' + type + 'Handler'](handler);
  return target;
}



//--------------------------------------------------
// SpreadSheet
//--------------------------------------------------

function getOrCrreateSheet_(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (! sheet) {
    sheet = ss.insertSheet(sheetName);
  } else {
    sheet.clear();
  }
  return sheet;
}

// Write by 1 cell. To Avoid screen crash when write more than 250 cells into Range#setValues...
function SheetValuesWriter_(sheet, row, col) {
  this.sheet = sheet;
  this.startRow = row || 1;
  this.startCol = col || 1;
  this.currentRow = row || 1;
  this.currentCol = col || 1;
  this.currentCell = this.sheet.getRange(this.currentRow, this.currentCol);
}
SheetValuesWriter_.prototype = {
  CellProperties: 'value'.split(''),
  setRange: function(row, col) {
    this.currentRow = row;
    this.currentCol = col;
    this.currentCell = this.sheet.getRange(this.currentRow, this.currentCol);
  },
  newRow: function() {
    this.setRange(this.currentRow + 1, this.startCol);
  },
  writeCell: function(value, row, col) {
    if (row != null && col != null) {
      this.setRange(row, col);
    }
    var cell = this.currentCell;
    if (typeof value === 'string') {
      cell.setValue(value);
    } else {
      for (var i in value) {
        if (isArray_(value[i])) {
          var args = value[i];
          callMethod_(cell, 'set' + toUpperCaseFirstChar_(i), args);
        } else {
          cell['set' + toUpperCaseFirstChar_(i)](value[i]);
        }
      }
    }
    this.currentCell = this.sheet.getRange(this.currentRow, ++this.currentCol);
  },
  writeValues: function(rows) {
    for (var i = 0, rowLen = rows.length; i < rowLen; i++) {
      var cells = rows[i];
      for (var j = 0, colLen = cells.length; j < colLen; j++) {
        this.writeCell(cells[j]);
      }
      this.newRow();
    }
  }
};



//--------------------------------------------------
// Common Functions
//--------------------------------------------------
function isArray_(obj) {
  return Object.prototype.toString.call(obj) === '[object Array]';
}

function toUpperCaseFirstChar_(str) {
  return str[0].toUpperCase() + str.substring(1);
}

// GAS Objects with obj[methodName].apply(obj, args) cause error.
function callMethod_(obj, methodName, args) {
  var fnBody = 'obj.' + methodName + '(' + args.join(',') + ')'; //Args changed to String by join
  new Function('obj', fnBody)(obj);
}
June 11, 2018
·
Answer
·
Like
0
·
Follow
0
Martin Hawes 6
I think on line 231 that returns the picklist values.
pickVals.join('\n'),
I'm know that will list the pick list values for the pick list. How do I do the same thing to list the associated page layouts?
June 11, 2018
·
Like
0
·
Dislike
0
Martin Hawes 6
Can anyone help please? I've been trying to solve this for 2 days now. Thanks in advance
June 13, 2018
·
Like
0
·
Dislike
0
You need to sign in to do that.

Need an account? Sign Up
Have an account? Sign In
Dismiss
Browse by Topic

Welcome to Support!

Show

sorted by

Using Google script to list page layouts for each field returned

You need to sign in to do that.
