OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Splitting a phrase across cells

  • Thread starter Thread starter user25741535
  • Start date Start date
U

user25741535

Guest
UPDATED: Here are my requirements:

  • i want to input a phrase along the lines of: X_X_X_X_X_X_X_X_X_X in column D
  • i want the app scripts to run so that when i input this in the column DR from row 3 onwards to split this phrase along the columns starting from column F onwards.
  • i want it to skip the first X though
  • I want it so that when users update this phrase in column D it reflects straight away (onEdit)
  • however when this phrase is erased i dont want it to erase the data, i want it to stay
  • i am implementing this on a big sheet so i dont want it to affect anything already present so if theres blank cells i don't want it to do anything
  • i want it to run on a bunch of sheets so i would prefer the code references a specific sheet name say 'target sheet' at the moment its under active sheet so i dont want that as i dont want it to run on all the sheet=s.

I managed to write a code that works, it bypasses the data validation rules and meets all the criteria aboce. My only issue is now its super slow as everytime an edit is made in column DR then the whole sheet is reprocessed and iterates throughout the whole thing, How can i change this so that only edits the row thats affected? so only when a user changes a value in DR or adds a value will it run on that. Right now its way too slow due to the for loop

Code:
function final_split(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the specific sheet by name
  var sheet = spreadsheet.getSheetByName('Campaign'); // Replace 'Sheet5' with the actual name of your sheet
  
  // Get the range in column DR from row 3 onwards
  var startRow = 3;
  var dataRange = sheet.getRange('DR' + startRow + ':DR' + sheet.getLastRow());
  
  // Get the values from column DR starting from row 3
  var data = dataRange.getValues();
  
  // Iterate over each row in the range
  for (var i = 0; i < data.length; i++) {
    // Get the phrase in the current cell
    var phrase = data[i][0];
    
    // Check if the cell is not empty
    if (phrase) {
      // Split the phrase by underscore and skip the first element
      var parts = phrase.split('_').slice(1);
      
      // Set the values in the respective row from column F onwards
      for (var j = 0; j < parts.length; j++) {
        var cell = sheet.getRange(startRow + i, 6 + j); // Column F is column index 6
        
        // Save the current data validation rule
        var validationRule = cell.getDataValidation();
        
        // Clear data validation
        cell.setDataValidation(null);
        
        // Set the value
        cell.setValue(parts[j]);
        
        // Restore data validation
        if (validationRule) {
          cell.setDataValidation(validationRule);
        }
      }
    }
  }
}

<p>UPDATED:
Here are my requirements:</p>
<ul>
<li>i want to input a phrase along the lines of: X_X_X_X_X_X_X_X_X_X in column D</li>
<li>i want the app scripts to run so that when i input this in the column DR from row 3 onwards to split this phrase along the columns starting from column F onwards.</li>
<li>i want it to skip the first X though</li>
<li>I want it so that when users update this phrase in column D it reflects straight away (onEdit)</li>
<li>however when this phrase is erased i dont want it to erase the data, i want it to stay</li>
<li>i am implementing this on a big sheet so i dont want it to affect anything already present so if theres blank cells i don't want it to do anything</li>
<li>i want it to run on a bunch of sheets so i would prefer the code references a specific sheet name say 'target sheet' at the moment its under active sheet so i dont want that as i dont want it to run on all the sheet=s.</li>
</ul>
<p>I managed to write a code that works, it bypasses the data validation rules and meets all the criteria aboce. My only issue is now its super slow as everytime an edit is made in column DR then the whole sheet is reprocessed and iterates throughout the whole thing, How can i change this so that only edits the row thats affected? so only when a user changes a value in DR or adds a value will it run on that. Right now its way too slow due to the for loop</p>
<pre><code>

function final_split(e) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Get the specific sheet by name
var sheet = spreadsheet.getSheetByName('Campaign'); // Replace 'Sheet5' with the actual name of your sheet

// Get the range in column DR from row 3 onwards
var startRow = 3;
var dataRange = sheet.getRange('DR' + startRow + ':DR' + sheet.getLastRow());

// Get the values from column DR starting from row 3
var data = dataRange.getValues();

// Iterate over each row in the range
for (var i = 0; i < data.length; i++) {
// Get the phrase in the current cell
var phrase = data[0];

// Check if the cell is not empty
if (phrase) {
// Split the phrase by underscore and skip the first element
var parts = phrase.split('_').slice(1);

// Set the values in the respective row from column F onwards
for (var j = 0; j < parts.length; j++) {
var cell = sheet.getRange(startRow + i, 6 + j); // Column F is column index 6

// Save the current data validation rule
var validationRule = cell.getDataValidation();

// Clear data validation
cell.setDataValidation(null);

// Set the value
cell.setValue(parts[j]);

// Restore data validation
if (validationRule) {
cell.setDataValidation(validationRule);
}
}
}
}
}
</code></pre>
 

Latest posts

D
Replies
0
Views
1
Dhanushka Amarakoon
D
S
Replies
0
Views
1
Shikhar Ambashta
S
Top