Google Spreadsheet custom function to add csv in spreadsheet

1
votes

I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service.

I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes.

This is the custom function i have so far:

function fetchFromUrl() {
  var url = "https://disclosedurl";
  var response = UrlFetchApp.fetch(url, {
    "headers": {
      "Authorization": "OAUTH token",
    }
  });
  var result = response.getContentText();

  var csvData = CSVToArray(result, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+9, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}

When i call this function from a cell in the spreadsheet i get this error.

Error: You do not have permission to call setValues (line 56).

I've seen this issue pop up a lot but i haven't been able to fix it. Is there a better way to accomplish this or am i missing something in my custom function that grants permissions?

2
google-apps-scriptgoogle-sheetsgoogle-spreadsheet-api
I think the issue is with setValues(). I think you cannot modify other cell values when calling custom function from a spreadsheet cell. Hope that helps! – KRR
From the doc : A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead. – Serge insas

2 Answers

1
votes

Edit: since I didn't have your source data I went ahead and got a random CSV just to test everything how you had it and it is not done easily. It would be a really sloppy work around and not really worth it.

Why were you choosing to try a custom formula in this instance?

The scripts I copied below were something close to what you were doing but with importing spreadsheets instead of CSV's.

function DoStuff(All) 
{
  var sheet    = SpreadsheetApp.getActive().getSheetByName('Main');          //Syncs sheet by name into var
  var StartRow = 33;
  var StartCol = 1;
  var AddRows  = 12;
  var AddCols  = 3;
  var DD     = sheet.getSheetValues(StartRow,StartCol,AddRows,AddCols);    //Build array from data off of sheet into variable -- getSheetValues(Row,Col,Add-Rows,Add-Cols)
  for (var i = 0, DDL = DD.length; i < DDL; i++)                                      //Builds 2d Looping-Array to allow choosing of columns at a future point
    {
      var col     = DD[i];                                                   //Retrieves all columns from array
      var SheetID = col[1];                                                    //Returns certain column (Starts counting columns at 0)
      var ImportedSheet = col[2];                                              //Returns certain column (Starts counting columns at 0)
      var TimeFrame = col[0];                                                  //Returns certain column (Starts counting columns at 0)
      var FuncName = "DoStuff";
      var ImportedSheetEmpty = isEmpty_(ImportedSheet)  
      var SheetIDEmpty = isEmpty_(SheetID)  
      if (SheetIDEmpty == false && ImportedSheetEmpty == true) 
      { 
        var CurrentEmpty = getFirstEmptyRowUsingArray_('Database');
        var LocNum    = SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('J2:J').getValues();
        var LocNum    = cleanArray_(LocNum);
        var TotInvAmt = SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('H2:H').getValues();
        var InvDate   = SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('F2:F').getValues();
        var DistNTax  = SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('N2:S').getValues();
        var DistNTax  = SumColArray_(DistNTax);
        SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty,3,LocNum.length,   LocNum[0].length   ).setValues(LocNum).setNumberFormat('@STRING@');      //getRange(Row,Col,RowAdd,ColAdd)
        SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty,4,TotInvAmt.length,TotInvAmt[0].length).setValues(TotInvAmt);   //getRange(Row,Col,RowAdd,ColAdd)
        SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty,6,InvDate.length,  InvDate[0].length  ).setValues(InvDate);     //getRange(Row,Col,RowAdd,ColAdd)
        for (var d = 0; d < LocNum.length; d++)                                    //Builds 2d Looping-Array to allow choosing of columns at a future point
        {
          SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty + d,1).setValue(FuncName);    //getRange(Row,Col,RowAdd,ColAdd)
          SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty + d,2).setValue(TimeFrame);   //getRange(Row,Col,RowAdd,ColAdd)
          SpreadsheetApp.getActive().getSheetByName('Database').getRange(CurrentEmpty + d,5).setValue(DistNTax[d]);   //getRange(Row,Col,RowAdd,ColAdd)
        }
        sheet.getRange(StartRow + i, 3).setValue("Successful");
      }
    }
  if(All!=1)
  {
    UpdateDashboard();
  }
  SpreadsheetApp.flush();                                            //Applies all pending Spreadsheet changes
}

­ ­The following are all my helper functions that I include in my projects in a separate .GS file to do stuff. ­ ­

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
// Subtract Days from Date Function
//
// if 'TimeFrame' is 7/20/2016 from 'var TimeFrame = col[0];' in a loop and you want to email someone 10 days before that date you would:
// subDaysFromDate_(TimeFrame,'10')
// would return 7/10/2016 

function subDaysFromDate_(date,d)
{
    if ( Object.prototype.toString.call(date) === "[object Date]" ) 
  { //date it is a date object
    if ( isNaN( date.getTime() ) ) 
    {  // d.valueOf() could also work
      Logger.log('Inbound date error #1: ' + date);  // date is not valid
      return
    }
    else 
    {
      var result = new Date(date.getTime()-d*(24*3600*1000));
      return result
      }
  }
  else
  {
    Logger.log('inbound date error #2: ' + date);
    return
  }
}

//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//Script Based ImportRange

//Example importRange_('0AodPsgg.......................','Sheet1','A:G','Common','C7','y')
//Explanation importRange_('Importing Spreadsheet Key','Importing Spreadsheet Tab Name','Importing Spreadsheet Tab's Range','Destination Spreadsheet Tab Name','Destination Spreadsheet Tab's placement','Will add note to the first cell of import')

function importRange_(Source_Key,Source_Sheet,Source_Range,Set_Sheet,Set_Pos,Add_Note) 
{
  var Load = SpreadsheetApp.openById(Source_Key).getSheetByName(Source_Sheet).getRange(Source_Range).getValues();
  var Name = SpreadsheetApp.openById(Source_Key).getName();
  var RowVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getRow();
  var ColVal = SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_Pos).getColumn();
  if(Add_Note.toUpperCase() == 'Y')
  {
    SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,1,1).setNote("Import Script Updated On: " + Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy hh:mm a")+"\nSS Name: "+Name+"\nRange: "+Source_Sheet+"!"+Source_Range+"\nSS Key: "+ Source_Key);      
  }
  SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(RowVal,ColVal,Load.length,Load[0].length).setValues(Load);
  SpreadsheetApp.flush();
  SpreadsheetApp.getActiveSpreadsheet().toast('At: '+Set_Sheet+'!'+Set_Pos,'Import Completed:');
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on isEmpty_()
//Find Last Row on Database
function getFirstEmptyRowUsingArray_(sheetname) 
    {
      var data = SpreadsheetApp.getActive().getSheetByName(sheetname).getDataRange().getValues();
      for(var n = data.length ; n<0 ;  n--)
      {
        if(isEmpty_(data[n][0])=false)
        {
          n++;
          break;
        }
      }
      n++
        return (n);
    }
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on isEmpty_()
// Blank Array Extractor/Rebuilder
function cleanArray_(actual)
{
    var newArray = new Array();
    for(var i = 0, aL = actual.length; i<aL; i++)
    {
        if (isEmpty_(actual[i]) == false)
        {
            newArray.push(actual[i]);
        }
    }
    return newArray;
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
// Even/Odd
function isEven_(value) {
    if (value%2 == 0)
        return true;
    else
        return false;
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on cleanArray_()
// Array Col Sum Agent
function SumColArray_(sumagent)
{
    var newArray = new Array();
    for(var i = 0, sL = sumagent.length; i<sL; i++)
    {
       var totalsum = 0
       var CleanForSum = cleanArray_(sumagent[i]);
       for(var d = 0, CFSL = CleanForSum.length; d<CFSL; d++)
       {  
        totalsum += CleanForSum[d];
       }
      newArray.push(Math.round(totalsum));
    }
    return newArray;
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
// Empty String Check
function isEmpty_(string) 
{

    if(!string)             return true;         
    if(string == '')        return true;
    if(string === false)    return true; 
    if(string === null)     return true; 
    if(string == undefined) return true;
    string = string+' '; // check for a bunch of whitespace
    if('' == (string.replace(/^\s\s*/, '').replace(/\s\s*$/, ''))) return true;       
    return false;        
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on isEmpty_() && getFirstEmptyRowUsingArray_()
// Script Look-up
/*
Benefit of this script is:
-That google sheets will not continually do lookups on data that is not changing with using this function
-Unlike Vlookup you can have it look at for reference data at any point in the row.  Does not have to be in the first column for it to work like Vlookup.

Useage:

var LocNum    = SpreadsheetApp.openById(SheetID).getSheetByName('Sheet1').getRange('J2:J').getValues();
FinderLookUpReturnArrayRange_(LocNum,0,'Data','A:G',[3],'test',1,1,'No');


-Loads all Locations numbers from J2:J into a variable 
--looks for Location Numbers in Column 0 of Referance sheet and range eg "Data!A:G"
---Returns results to Column 3 of Target Sheet and range eg "test!A1" or "1,1"

*/


function FinderLookUpReturnArrayRange_(Search_Key,SearchKey_Ref_IndexOffSet,Ref_Sheet,Ref_Range,IndexOffSetForReturn,Set_Sheet,Set_PosRow,Set_PosCol,ReturnMultiResults)   
{
  var twoDimensionalArray = [];
  var data = SpreadsheetApp.getActive().getSheetByName(Refv_Sheet).getRange(Ref_Range).getValues();         //Syncs sheet by name and range into var
  for (var i = 0, Il=Search_Key.length; i<Il; i++)                                                         // i = number of rows to index and search  
  {
    var Sending = [];                                                                                      //Making a Blank Array
    var newArray = [];                                                                                     //Making a Blank Array
    var Found ="";
    for (nn=0,NNL=data.length;nn<NNL;nn++)                                                                 //nn = will be the number of row that the data is found at
    {
      if(Found==1 && ReturnMultiResults=='No')                                                                                         //if statement for found if found = 1 it will to stop all other logic in nn loop from running
      {
        break;                                                                                             //Breaking nn loop once found
      }
      if (data[nn][SearchKey_Ref_IndexOffSet]==Search_Key[i])                                              //if statement is triggered when the search_key is found.
      {
        var newArray = [];
        for (var cc=0,CCL=IndexOffSetForReturn.length;cc<CCL;cc++)                                         //cc = numbers of columns to referance
        {
          
          var iosr = IndexOffSetForReturn[cc];                                                             //Loading the value of current cc
          var Sending = data[nn][iosr];                                                                    //Loading data of Level nn offset by value of cc
          if(isEmpty_(Sending)==true)                                                                      //if statement for if one of the returned Column level cells are blank
          {
          var Sending =  "#N/A";                                                                           //Sets #N/A on all column levels that are blank
          }
          if (CCL>1)                                                                                       //if statement for multi-Column returns
          {
            
            newArray.push(Sending);
            if(CCL-1 == cc)                                                                                //if statement for pulling all columns into larger array
            {
              twoDimensionalArray.push(newArray);
              //Logger.log(twoDimensionalArray);
              var Found = 1;                                                                              //Modifying found to 1 if found to stop all other logic in nn loop
              break;                                                                                      //Breaking cc loop once found
            }
          }
          else if (CCL<=1)                                                                                 //if statement for single-Column returns
          {
            twoDimensionalArray.push(Sending);
            var Found = 1;                                                                                 //Modifying found to 1 if found to stop all other logic in nn loop
            break;                                                                                         //Breaking cc loop once found
          }
        }
      }
      if(NNL-1==nn && isEmpty_(Sending)==true)                                                             //following if statement is for if the current item in lookup array is not found.  Nessessary for data structure.
      {
        for(var na=0,NAL=IndexOffSetForReturn.length;na<NAL;na++)                                          //looping for the number of columns to place "#N/A" in to preserve data structure
        {
          if (NAL<=1)                                                                                      //checks to see if it's a single column return
          {
            var Sending = "#N/A";
            twoDimensionalArray.push(Sending);
          }
          else if (NAL>1)                                                                                  //checks to see if it's a Multi column return
          {
            var Sending = "#N/A";
            newArray.push(Sending);
          }
        }
        if (NAL>1)                                                                                         //checks to see if it's a Multi column return
        {
          twoDimensionalArray.push(newArray);  
        }
      }
    }
  }
  if(typeof Set_PosRow != "number")                                                                        //checks to see if what kinda of variable Set_PosRow is.  if its anything other than a number it will goto next avaible row
  {
    var Set_PosRow = getFirstEmptyRowUsingArray_(Set_Sheet);                                               //for usage in a database like entry without having to manually look for the next level.
  }
  for (var l = 0,lL=Search_Key.length; l<lL; l++)                                                          //Builds 2d Looping-Array to allow choosing of columns at a future point
  {
    if (CCL<=1)                                                                                            //checks to see if it's a single column return for running setValue
    {
      SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow + l,Set_PosCol).setValue(twoDimensionalArray[l]);
    }
  }
    if (CCL>1)                                                                                             //checks to see if it's a multi column return for running setValues
    {
      SpreadsheetApp.getActive().getSheetByName(Set_Sheet).getRange(Set_PosRow,Set_PosCol,twoDimensionalArray.length,twoDimensionalArray[0].length).setValues(twoDimensionalArray);
    }
  SpreadsheetApp.flush();
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on pad_()

//Build a list of warehouse email address.

//example 'var CHeckMails = WhseEmailPasser_(LocNum,["GM","ADMIN","SHOP"],"W");'
function WhseEmailPasser_(IncomingWhseNum, WhseEmails, WorD)
{
  var EmailArray = [];  
  if (IncomingWhseNum < 1000)
  {
    var PadWhseNum = pad_(IncomingWhseNum,3);
  }
  else if (IncomingWhseNum >= 1000)
  {
    var PadWhseNum = pad_(IncomingWhseNum,5);
  }
  for(var w=0, wL = WhseEmails.length; w<wL; w++)
  {
    var CurrentEmail = WhseEmails[w].toUpperCase();
    if(CurrentEmail=="HR")
    {
      var Email = CurrentEmail + PadWhseNum + "@Company.COM"; 
      EmailArray.push(Email);
      continue;
    }
    var Email = WorD + PadWhseNum + CurrentEmail + "@Company.COM";
    EmailArray.push(Email);
  }
  EmailArray.push("");  //Needed for the last comma when used in emailer function.
  return EmailArray.toString();
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
// Pad a number with x amound of Zero

//example 'var PadWhseNum = pad_(IncomingWhseNum,5);'
function pad_(n, width, z)
{
  z = z || '0';
  n = n + '';
  return n.length >= width ? n : new Array(width - n.length + 1).join(z) + n;
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
function frenchDate_(date) 
{
  if ( Object.prototype.toString.call(date) === "[object Date]" ) 
  { //date it is a date object
    if ( isNaN( date.getTime() ) ) 
    {  // d.valueOf() could also work
      Logger.log('Inbound date error #1: ' + date);  // date is not valid
      return
    }
    else 
    {
      var month = ['janvier','février','mars','avril','mai','juin','juillet','août','septembre','octobre','novembre','décembre'];
      var day = ['dimanche','lundi','mardi','mercredi','jeudi','vendredi','samedi'];
      var m = month[date.getMonth()];
      var d = day[date.getDay()];
      var dateStringFr = d+' '+date.getDate()+' '+m+' '+date.getFullYear();
      return dateStringFr
    }
  }
  else
  {
    Logger.log('inbound date error #2: ' + date);
    return
  }
}
  
/*
you want to use the following format:

le xxxxx 15 cccc 2015.

replace xxxxx with days  (no capitals for weekdays in french)

Monday = lundi
Tuesday = mardi
Wednesday = mercredi
Thursday = jeudi
Friday = vendredi
Saturday = samedi
Sunday = dimanche

replace cccc with the month ( again, no capital )
January = janvier
February = février 
March = mars
April = avril
May = mai
June = juin
July = juillet
August = août
September = septembr
October = octobre 
November = novembre
December = décembre 
*/
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//--//Dependent on getColumnOffsetOfCurrentParameter_()
//Script version to conditional Formatting.
//e.g. 'setRowColors_('Status',0);'
function setRowColors_(TargetColumnHeaderContent,TargetRowForHeader)
{
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var TargetColumnOffset = getColumnOffsetOfCurrentParameter_(TargetColumnHeaderContent,TargetRowForHeader);
  if (typeof TargetColumnOffset === 'undefined')
  {
    Logger.log("Column Header not found");
    return
  }
  for (var i = range.getRow(); i < range.getLastRow(); i++) 
  {
    var rowRange = range.offset(i, 0, 1);
    var status = rowRange.offset(0, TargetColumnOffset).getValue();
    if (status == 'Completed')
    {
      rowRange.setBackgroundColor("#DEAD01");  //goldenRod
    }
    else if (status == 'In Progress')
    {
      rowRange.setBackgroundColor("#FFDD88");  //Dim Yellow
    }
    else if (status == 'Not Started')
    {
      rowRange.setBackgroundColor("#CC6666");  //maroon 
    }
  }
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
//Returns the offset value of the column (Row 1) titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getColumnOffsetOfCurrentParameter_(ColumnHeader,Row)
{
  Row = typeof Row !== 'undefined' ? Row : 0;  //Pre-Defined Row 1 as the title line if user does not load the second parameter with a Row Number call.
  var lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
  for (var i = 0; i < range.getLastColumn(); i++)
  {
    if (range.offset(Row, i, 1, 1).getValue() == ColumnHeader)
    {
      return i;
    }
  }
}
//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`



//~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
0
votes

I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image.

The following error message is displayed: You do not have permission  
to call setValue. The reason as to why it’s not allowed is beyond me but
the same question was asked on the Google support forums where a Google 
employee simply stated it wasn’t possible. The workarounds that he 
suggested were “…running your script inside the editor, or from a drawing

Link for reference: http://jonrh.is/oddities-in-scripting-for-google-docs-spreadsheet/