Some helper functions for IGSV using Google Script

Hi meitar and thank for the awesome plugin! Having a great time learning the ins and outs of everything. Thanks for making it free, its much appreciated in a world of monthly SaaS or premium plugins.

I would like to give something back in the form of a few helper functions for newbies to use in google scripts. With that said i am only a self taught programmer so my functions may not be the most efficient or optimized way to do things, but hey its free 🙂

 // @link https://gist.github.com/5520691
//gets column number based on header title great for dynamically creating charts
 
function testGetColumnNrByName() {
  var sheet = SpreadsheetApp.getActiveSheet();
  Logger.log(getColumnNrByName_(sheet, '%COLUM_TITLE%'));

}

function getColumnNrByName(sheet, name) {
  var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var values = range.getValues();
  
  for (var row in values) {
    for (var col in values[row]) {
      if (values[row][col] == name) {
        return parseInt(col);
      }
    }
  }
  
  throw 'failed to get column by name';
}
//***********************************************

//Take a column number and gives you a letter
function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}//**************************************************************************************************

//Takes a column letter and gives you a number
function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}//**************************************************************************************************

//Gets G-file ID from url***********************************************************************
function getIdFrom(url) {
  var id = "";
  var parts = url.split(/^(([^:\/?#]+):)?(\/\/([^\/?#]*))?([^?#]*)(\?([^#]*))?(#(.*))?/);
  if (url.indexOf('?id=') >= 0){
     id = (parts[6].split("=")[1]).replace("&usp","");
     return id;
   } else {
   id = parts[5].split("/");
   //Using sort to get the id as it is the longest element. 
   var sortArr = id.sort(function(a,b){return b.length - a.length});
   id = sortArr[0];
   return id;
   }
 }
//*************************************************************************************************

//crops all manifest sheets to data 
      var sSheet = SpreadsheetApp.openByUrl(link);
      
      var sheetID = sSheet.getId();
      var numSheets = sSheet.getNumSheets();
      var sheets = sSheet.getSheets();
      var p;
      for(p=0; p < numSheets; p++){
        var shet = sheets[p];
        var range = shet.getDataRange();
        shet.setActiveSelection(range);
        var startCol = range.getLastColumn();
        var startRow = range.getLastRow();
        var endCol = shet.getMaxColumns();
        var endRow = shet.getMaxRows();
        if(endCol > startCol) shet.deleteColumns(startCol, endCol - startCol);
        if(endRow > startRow) shet.deleteRows(startRow, endRow - startRow);
      }      
//*****************************************************************

//YOU HAVE to add the XMLRPC library to your script for this to work - Library ID: "My_8O8KRa_MszCVjoC01DTlqpU7Swg-M5" 
      //Post to wordpress
      //creates an automated post on your wordpress blog about the data extracted in the program elsewhere
      var wordpress = {
        url: "http://yourURL.com/xmlrpc.php",
        username: "Your user name",  
        password: "your password"
      };
      // Call the metaWeblog.newPost API method to create a new blog post //
      var request = new XMLRPC.XmlRpcRequest(wordpress.url, 'metaWeblog.newPost');
      var cat = ['yourCategoryNameHere'];
      // The first parameter is empty since there's no blog ID for WordPress //
      request.addParam(""); 
      request.addParam(wordpress.username);
      request.addParam(wordpress.password);
      var status = "";
      // The blog post content. You can have HTML in the description.  
      //Only use finalized variables no calculations can be performed inside blogpost (WONT WORK -->  title: "Title - " + someVariable,) 
      //You have to do it... var someVariable = "Title - " + information; ... title: someVariable, <--WORKS  
//Dont try to mess with dateCreated parameter of metaweblog, it will fail when using GAS
      var blogPost = {
        post_type: 'post',
        post_status: 'publish',  // Set to draft or publish 
        title: yourTitleVariable, 
        categories: cat, //expects an array even if only a single value
        mt_allow_comments: 'closed',
        description: yourDescriptionVariable //create a description variable with HTML/javascript allowed (example shown below)
      };
      request.addParam(blogPost);
      var response = request.send().parseXML();
//**************************************************************

//Example of creating HTML description with google script
        var gdocTitle = '<div class="boxed"><h2> Example: ' + newFileName + '</h2><br><br>DescriptionDescriptionDescription.' + '[gdoc key="' + yourGoogleSheetIdVariable + '" title="' + yourTitleVariable
        + '" chart="pie" class="no-datatables" query="select B, sum(F) group by B order by sum(F)" chart_dimensions="3" chart_pie_slice_text="name" style="width: 900px; height: 900px;"]';

• This topic was modified 8 years, 5 months ago by theheirophant.
• This topic was modified 8 years, 5 months ago by theheirophant.
• This topic was modified 8 years, 5 months ago by theheirophant.