CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tristan C
Tristan C

Reputation: 119

Text Replacement using app script for google sheets

Issue

I am having some problems with text replacement using app script. I would like to get the desired output below but am currently having a few issues with additional commas and a lack of apostrophes.

Code

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var row_count = sheet.getLastRow();
  var input_data = sheet.getRange(1, 1, row_count, 15).getValues();
  var data = [];
  for (var i = 0; i < row_count; i++) {

      var row_data; 

        // Table Row
      if (input_data[i][0] == "table") {
        row_data = "INSERT INTO " + input_data[i][1];

        // Fields Row
      } else if (input_data[i][0] == "fields") {
        row_data = "(" + input_data[i] + ")";

        // Values Row
      } else if (input_data[i][0] == "values") {
        row_data = "VALUES";

        // Entry Row
      } else if (input_data[i][0] == "entry") {
        row_data = "(" + input_data[i] + ");";

        // Empty Row
      } else if (input_data[i][0] == "") {
        row_data = "";
      }

      data.push([row_data]);
  }
  sheet.getRange(1, 16, data.length, 1).setValues(data);
}

Data

enter image description here

Current Output

enter image description here

Desired Output

enter image description here

Upvotes: 0

Views: 80

Answers (1)

TheMaster
TheMaster

Reputation: 50383

Use array.join:

row_data = "(" + input_data[i].filter(e=> e!=="").join(", ") + ")";

Or

row_data = "('" + input_data[i].filter(e=> e!=="").join("', '") + "')";

Upvotes: 1

Related Questions