Google Sheet Auto Add / Update Value-CodePudding

I'm using Google Forms to collect a data. So, there was one question in the Google Form that I considered as a primary key when using the VLOOKUP function. However, there is a possibility that some data will collect zero (0) for the answer since they do not have an official ID number yet. I'm wondering if there is a way for me to automatically add or update a value when the value is zero (0). I'm thinking of using an temporary ID which letters and numbers. Ex: ID000001

CodePudding user response：

I believe your goal is as follows.

You want to convert 0 to ID000001. And, when the multiple 0 values are existing, you want to convert to ID000001, ID000002,,,.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:A"   sheet.getLastRow());
  let c = 1;
  const values = range.getValues().map(([a]) => [a === 0 ? `ID${(c  ).toString().padStart(6, '0')}` : a]);
  range.setValues(values);  
}

In this script, it supposes that the column of "STUDENT ID" is the column "A" because I cannot know the column information from your sample image.

References:

Added:

From your following additional question,

Wait. If I run again the script, the output is starting again with ID000001. Is there a way for me to just continue the number?

In this case, how about using PropertiesServices as follows?

Sample script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:A"   sheet.getLastRow());
  const p = PropertiesService.getScriptProperties();
  const count = p.getProperty("count");
  let c = count ? Number(count) : 1; // If you want to set the initial count, please modify 1 to other.
  const values = range.getValues().map(([a]) => [a === 0 ? `ID${(c  ).toString().padStart(6, '0')}` : a]);
  range.setValues(values);
  p.setProperty("count", c);
}

// When you want to reset the count, please run this function.
function reset() {
  PropertiesService.getScriptProperties().deleteProperty("count");
}

For example, when all ID###### values are updated every run, you can also use the following sample script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:A"   sheet.getLastRow());
  range.createTextFinder("^ID[\\d] $").matchEntireCell(true).useRegularExpression(true).replaceAllWith(0);
  let c = 1;
  const values = range.getValues().map(([a]) => [a === 0 ? `ID${(c  ).toString().padStart(6, '0')}` : a]);
  range.setValues(values);
}