Advertisement
RemcoE33

Fill blanks 2.0

Jun 22nd, 2021
104
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onOpen(e){
  2.   SpreadsheetApp.getUi().createMenu('FillBlanks')
  3.     .addItem('Fill fixed range', 'fillBlanksFixed')
  4.     .addItem('Fill active range', 'fillBlanksActive')
  5.     .addItem('Fill blanks offset check', 'fillBlanksActiveOfsetCheck')
  6.     .addToUi()
  7. }
  8.  
  9. function fillBlanksFixed(){
  10.   const ss = SpreadsheetApp.getActiveSpreadsheet()
  11.   const sheet = ss.getSheetByName('Data')
  12.   const values = sheet.getRange(4,1,sheet.getLastRow(),5).getValues()
  13.   const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy");
  14.   for (let i = 0; i < values.length; i++){
  15.     if(values[i][4] == "" && values[i][0] != ""){
  16.       sheet.getRange(i+4,5).setValue(date)
  17.     }
  18.   }
  19.  
  20. }
  21.  
  22. function fillBlanksActive(){
  23.   const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  24.   const values = sheet.getActiveRange().getValues().flat();
  25.   const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy");
  26.   const output = [];
  27.  
  28.   values.forEach(row => {
  29.     if (row != ''){
  30.       output.push([row])
  31.     } else {
  32.       output.push([date])
  33.     }
  34.   })
  35.  
  36.   sheet.getActiveRange().setValues(output)
  37.  
  38. }
  39.  
  40.  
  41. function fillBlanksActiveOfsetCheck(){
  42.   const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  43.   const values = sheet.getActiveRange().getValues().flat();
  44.   const offsetValues = sheet.getActiveRange().offset(0,-4).getValues().flat()
  45.   const date = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy");
  46.   const output = [];
  47.  
  48.   for (i = 0; i < values.length; i++){
  49.     if(values[i] == '' && offsetValues[i] != ''){
  50.       output.push([date])
  51.     } else {
  52.       output.push([values[i]])
  53.     }
  54.   }
  55.  
  56.   sheet.getActiveRange().setValues(output)
  57.  
  58. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement