google-apps-script - Increment only non-empty cells in a range with a single button on Google Sheets

Question

I want to loop through a range of cells of a column and increment only the non-empty cells by 1, with a click of a single button.

I tried a code that was later found to be for VBA Excel and not for Google Sheets; which I'm currently looking for.

The blank cells should remain unaffected. Thank you.

标签： google-apps-scriptgoogle-sheets

Answer 1

使用单个按钮增加非空单元格

function incrementNonEmptyCellsInARange(rA1) {
  var rA1=rA1 || 'A1:C3';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var change=false;
  try {
    var rg=sh.getRange(rA1);
  }
  catch(e) {
    SpreadsheetApp.getUi().alert(e + '\r\n Current Range: ' + rA1);
    return;
  }
  if(rg) {
    var vA=rg.getValues();
    for(var i=0;i<vA.length;i++) {
      for(var j=0;j<vA[i].length;j++) {
        if(typeof vA[i][j] == 'number') {
          vA[i][j]+=1;
          change=true;
        }
      }
    }
    if(change) {
      rg.setValues(vA);
    }
  }
}

这是按钮

和一个文本框，用于以 A1 表示法输入所需范围。

function showMySideBar() {
  var html='Range: <input type="text" id="txt1" /><br /><input type="button" value="Increment" onClick="doIt();" />';
  html+='<script>function doIt(){var txt=document.getElementById("txt1").value;google.script.run.incrementNonEmptyCellsInARange(txt);}console.log("My Code");</script>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}