CODEWITHSUNDEEP
for-loopgoogle-apps-scriptgoogle-sheets
onit
onit

Reputation: 2372

How to repeat the values until next non-empty row and then repeat next row's data and so on with Google Apps Script?

The challenge here is to capture the customer ID and its name and repeat it down below until the script reaches the next non-empty row. Then, the next ID and customer name are the ones that will be repeated.

This is just how far I've gotten on my own, but I can't build the core of the logic mylself (yes).

function repeatValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const originData = sheet.getRange(6, 1, sheet.getLastRow(), 3).getValues();
  const targetSheet = ss.getSheetByName("Sheet2");

  for (var a = 0; a < originData.length; a++) {
      if (originData[a][0] != ''){
        var customerID = originData[a][0];
        var customer = originData[a][1];
      }
      if (originData == ''){
        targetSheet.getRange(2,1,originData.length, originData.length).setValue(customerID);
      }        
      }
  }

Here's a link to the sample spreadsheet: https://docs.google.com/spreadsheets/d/1wU3dql6dnh_JBC6yMkrFzEYxdyzyNdkBmve2pfyxG0g/edit?usp=sharing

Expected Result includes these values in blue repeated: enter image description here

Any help is appreciated!

Upvotes: 0

Views: 424

Answers (1)

Tanaike
Tanaike

Reputation: 201408

I believe your goal as follows.

  • You want to achieve the following situation. (The following image is from your question.) You want to add the texts of blue font color.

  • You want to put the values to "Sheet2".

Modification points:

  • In your script, at const originData = sheet.getRange(6, 1, sheet.getLastRow(), 3).getValues();, the start row is 6. In this case, please modify sheet.getLastRow() to sheet.getLastRow() - 5
  • In order to create an array for putting to "Sheet2", it is required to check the columns "A" and "B" of "Sheet1". For this, in this modification, I used temp as follows.

When above points are reflected to your script, it becomes as follows.

Modified script:

function repeatValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const originData = sheet.getRange(6, 1, sheet.getLastRow() - 5, 3).getValues(); // Modified
  const targetSheet = ss.getSheetByName("Sheet2");
  
  // I added below script.
  let temp = ["", ""];
  const putValues = originData.map(([a, b, c]) => {
    if (a.toString() && b.toString() && temp[0] != a && temp[1] != b) {
      temp = [a, b];
    }
    return temp.concat(c);
  });
  targetSheet.getRange(2, 1, putValues.length, putValues[0].length).setValues(putValues);
}

Note:

  • This modified script is for your sample Spreadsheet. So when the structure of Spreadsheet is changed, the script might not be able to be used. Please be careful this.

Reference:

Upvotes: 1

Related Questions