Generate PDF for beneficiary form

Please go to Livelihoods Monitoring Folder > OTHER > 4_Trainings > Livelihoods guides > PDM Master Surveys and find PRINTOUT CUSTOMISED-Livelihoods Beneficiary Survey file

../_images/main_file.png

Please follow the instructions and explanations on README sheet:

  • Manual Modifications

    1. The orange part in survey and choice sheets are modified from the original xsl forms

    ../_images/step1.png

    1. Pull and search function in calculation columns are not acceptable, they should be replaced.

    2. Choices sheet cannot have exlabel, they should be replaced.

    3. Default column is modified

    4. Appearance column is modified

  • Automatic Modifications

The script is located in the same file, please fo to Tools > Script Editor

In order to fix the known format issues with the labels, it’s required to run one-by-one all the following functions:

BR()

The function removes <br> tag from labels

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
function BR() {
  var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(thisid);
  var sheet= ss.getSheetByName("survey");
  var x=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  
  for(iy=0;iy<x.length;iy++)
  {
    for(ix=0;ix<x[0].length;ix++)
    {
      
      try{x[iy][ix]=x[iy][ix].replace(/<br>/g,"");
          x[iy][ix]=x[iy][ix].replace(/<\/br>/g,"");
          x[iy][ix]=x[iy][ix].replace("###<span","#<span");
          x[iy][ix]=x[iy][ix].replace("#<span","###<span");
         }catch(e){}
    }
  }
  sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).setValues(x);
}
deletespace()

The function removes n tag from labels

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
function deletespace() {
  var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(thisid);
  var sheet= ss.getSheetByName("survey");
  var x=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  
  for(iy=0;iy<x.length;iy++)
  {
    for(ix=0;ix<x[0].length;ix++)
    {
      
      try{var y=x[iy][ix].match(/>.*?\n.*?</g);
          
          if(y!=null)
          {
            for (i=0;i<y.length;i++)
            {
              if(y[i].match(/>\n</)!=null){continue;}
              Logger.log(y[i]);
              var yy=y[i].replace(/\n/,"");
              x[iy][ix]=x[iy][ix].replace(y[i],yy);
            }
          }
          
         }catch(e){}
    }
  }
  sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).setValues(x);
}
insertspace()

The function inserts n tag

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
function insertspace() {
  var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(thisid);
  var sheet= ss.getSheetByName("survey");
  var x=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  
  for(iy=0;iy<x.length;iy++)
  {
    for(ix=0;ix<x[0].length;ix++)
    {
      
      try{x[iy][ix]=x[iy][ix].replace(/></g,">\n<");
         }catch(e){}
    }
  }
  sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).setValues(x);
}
Note()

The function removes n tag for Note

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
function Note() {
  var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();
  var ss = SpreadsheetApp.openById(thisid);
  var sheet= ss.getSheetByName("survey");
  var x=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
  
  for(iy=0;iy<x.length;iy++)
  {
    for(ix=0;ix<x[0].length;ix++)
    {
      
      try{x[iy][ix]=x[iy][ix].replace(/NOTE: <\/span>\n/g,"NOTE: </span>");
         }catch(e){}
    }
  }
  sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).setValues(x);
}