Automating Data Fill-Down in Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m trying to figure out how to automate a task in Google Sheets using Apps Script. I need to populate Column D with item numbers. The tricky part is that each item number should fill down to cover its associated rows of data. Here’s what I’m aiming for:

  1. Start at the first item number in Column D
  2. Fill that number down until the next item number
  3. Repeat this process for all items

I’ve been scratching my head over this one. Any ideas on how to tackle it with Apps Script? I’m pretty new to coding, so a simple explanation would be super helpful. Thanks in advance for any tips or code snippets you can share!

function fillDownItemNumbers() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var columnD = sheet.getRange('D2:D' + lastRow);
  
  // Need help figuring out the logic here
  // How to identify item numbers and fill them down?
}

I’d really appreciate any guidance on this!

2

hey sophialee92, i think i can help u out! heres a quick script that should do the trick:

function fillDownItemNumbers() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange('D2:D' + lastRow).getValues();
  var fillValue = '';

  for (var i = 0; i < values.length; i++) {
    if (values[i][0] !== '') {
      fillValue = values[i][0];
    }
    values[i][0] = fillValue;
  }

  sheet.getRange('D2:D' + lastRow).setValues(values);
}

hope this helps! let me know if u need anything else

3

I’ve encountered a similar challenge before, and here’s an approach that worked well for me:

function fillDownItemNumbers() {
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange(‘D2:D’ + lastRow);
var values = range.getValues();
var result = ;
var currentItem = ‘’;

for (var i = 0; i < values.length; i++) {
if (values[i][0] !== ‘’) {
currentItem = values[i][0];
}
result.push([currentItem]);
}

range.setValues(result);
}

This script should handle the fill-down process efficiently. It iterates through the values, updates the current item when a new one is found, and fills down until the next item number. Give it a try and see if it solves your problem.

4

I’ve dealt with this exact issue before, and I can tell you it’s a bit tricky at first. Here’s what worked for me:

function fillDownItemNumbers() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange(‘D2:D’ + sheet.getLastRow()).getValues();
var fillDown = ‘’;

for (var i = 0; i < data.length; i++) {
if (data[i][0] !== ‘’) {
fillDown = data[i][0];
} else {
data[i][0] = fillDown;
}
}

sheet.getRange(‘D2:D’ + sheet.getLastRow()).setValues(data);
}

This script basically checks each cell in column D. If it finds a value, it uses that as the new ‘fill’ value. If the cell is empty, it fills it with the current ‘fill’ value. It’s pretty efficient and should work for large datasets too.

One tip: make sure your item numbers in column D are actually entered as values and not formulas. Otherwise, this might not work as expected. Hope this helps!