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 in google sheets, app scripts

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

user25741535

Guest
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 D 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.

This biggest issue is the sheet i'm implementing this on has a lot of data validation drop down ranges and so when i run my code if it doesn't meet the data validation then it doesn't work. I want it to override this and flag it as an error within the sheet itself like the default data val error where it says N/A input error etc so then users can go update it etc.

Code:
function connector () {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  
  // Get the last row with data in column D
  var lastRow = sheet.getLastRow();

  // Define the starting and ending column indexes
  var startColumn = 6; // Column F
  var endColumn = 34; // Column AH
  var maxColumns = endColumn - startColumn + 1; // Total number of columns to populate
  
  // Loop through each cell in column DM from row 3 to the last row
  for (var row = 3; row <= lastRow; row++) {
    var dataToSplit = sheet.getRange("D" + row).getValue();
    
    if (dataToSplit) { // Proceed only if there's data to split
      // Split the data by "_"
      var splitData = dataToSplit.split("_");
      
      // Check if there's more than one part to split (at least one "_")
      if (splitData.length > 1) {
        // Remove the first element (first part before the first "_")
        splitData.shift();
      } else {
        // If there's only one part (no "_"), clear the corresponding row range
        sheet.getRange(row, startColumn, 1, maxColumns).clearContent();
        continue; // Move to the next row
      }
      
      // Clear existing content in the corresponding row range from column F to AH
      sheet.getRange(row, startColumn, 1, maxColumns).clearContent();
      
      // Place each part into adjacent cells starting from column F
      for (var i = 0; i < splitData.length && i < maxColumns; i++) {
        sheet.getRange(row, startColumn + i).setValue(splitData[i]);
      }
    }
  }
}

Continue reading...
 

Latest posts

Top