Can u help write a script to add numebr to cells?

Question

I need a script that can be applied to the AB4:AB range of each sheet in Google Sheets. It can skip blank cells, and add "1" to the beginning of each cell that starts with a number, but skip the cells that start with "1". Thanks for ur help! Here's a sample: https://docs.google.com/spreadsheets/d/12Ux4BdcvWbtCzh9HMS0XIshetaENvxizQY47K4Q8QNQ/edit?usp=sharing

Answer 1

I believe your goal is as follows.

• You have values in cells "AB4:AB" in all sheets in a Google Spreadsheet.
• You want to add 1 to each value when the 1st letter of the value is the number and not 1.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  sheets.forEach(sheet => {
    const range = sheet.getRange("AB4:AB" + sheet.getLastRow());
    const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
    range.setValues(values);
  });
}

• When this script is run, the column "AB" is overwritten by the above-expected result.

• If you want to exclude the sheet using the script, please test the following script. In this case, the sheets except for excludeSheets are used.

  function myFunction() {
    const excludeSheets = ["sheet1",,,]; // Please set the exclude sheet names you want.
  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheets = ss.getSheets();
    sheets.forEach(sheet => {
      if (excludeSheets.includes(sheet.getSheetName())) {
        return;
      }
      const range = sheet.getRange("AB4:AB" + sheet.getLastRow());
      const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
      range.setValues(values);
    });
  }
  

• Although I cannot find your question, if you want to also add 1 when the 1st letter is (, please modify as follows.

  • From

      const values = range.getDisplayValues().map(([e]) => [!isNaN(e[0]) && e[0] != "1" ? `1${e}` : e]);
    

  • To

      const values = range.getDisplayValues().map(([e]) => [((!isNaN(e[0]) && e[0] != "1") || e[0] == "(") ? `1${e}` : e]);
    

Reference:

• map()