App Script GSheets 2- split to cells

################ Extract Markdown Info: Prompt - Part 1 (extract settings) ###################

Create a JS script for google sheets to extract specific information from the Markdown text in the column A and splits it into separate columns in the sheet for that row.

The address of Google Sheet:
https://docs.google.com/spreadsheets/d/1rFVnOALJjVzS-RUwVxt6TFwCvjOlyFFP3CFjXeBHtso/edit#gid=0

The name of an active sheet: ZW import.

The content to be extracted and splitted is located in the A2 cell and below cells (A3, A4, and so on).

The first part of the content to be extracted is located in the row that begins with "@ Main model used:". This row should be pulled in the respective row in the the column B.

The 2nd part of the content to be extracted is located in the row that begins with "@ OpenAI Status:". This row should be pulled in the respective row in the the column C.

The 3rd part of the content to be extracted is located in the row that begins with "@ Settings used:". This row should be pulled in the respective row in the the column D.

The 4rth part of the content to be extracted is located in the row that begins with "@ Midjourney AI Image Prompt:". This row should be pulled in the respective row in the the column E.

The 5th part of the content to be extracted is located in the row that begins with "@ Meta Description:". This row should be pulled in the respective row in the the column G.

The 6th part of the content to be extracted is located in the row that begins with "#". This row should be pulled in the respective row in the the column H.


**************** Extract Markdown Info: Script -  Part 1 (extract settings) **********************

***

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Import Text Files', 'importTextFiles')
    .addItem('Extract Markdown Info', 'extractMarkdownInfo')
    .addToUi();
}

function importTextFiles() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1rFVnOALJjVzS-RUwVxt6TFwCvjOlyFFP3CFjXeBHtso/edit#gid=0');
  var sheet = ss.getSheetByName('ZW import'); // Change Sheet1 to your sheet's name if it's different
  var folder = DriveApp.getFolderById('1o743ScrSFmnztwL5PBbxmd2mFclPiidO'); // Change 'folderID' with your Google Drive Folder ID where the text files are located
  var files = folder.getFilesByType(MimeType.PLAIN_TEXT);
  var i = 2; // Start at cell A2
  while (files.hasNext()) {
    var file = files.next();
    var text = file.getBlob().getDataAsString();
    sheet.getRange('A' + i++).setValue(text);
  }
}

function extractMarkdownInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('ZW import'); // change this to the name of your sheet if different

  var dataRange = sheet.getRange("A2:A");
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; i++) {
    var markdownText = data[i][0];
    var lines = markdownText.split("\n");

    for (var j = 0; j < lines.length; j++) {
      var line = lines[j];

      if (line.startsWith("@ Main model used:")) {
        sheet.getRange(i + 2, 2).setValue(line.replace("@ Main model used:", "").trim());
      } else if (line.startsWith("@ OpenAI Status:")) {
        sheet.getRange(i + 2, 3).setValue(line.replace("@ OpenAI Status:", "").trim());
      } else if (line.startsWith("@ Settings used:")) {
        sheet.getRange(i + 2, 4).setValue(line.replace("@ Settings used:", "").trim());
      } else if (line.startsWith("@ Midjourney AI Image Prompt:")) {
        sheet.getRange(i + 2, 5).setValue(line.replace("@ Midjourney AI Image Prompt:", "").trim());
      } else if (line.startsWith("@ Meta Description:")) {
        sheet.getRange(i + 2, 7).setValue(line.replace("@ Meta Description:", "").trim());
      } else if (line.startsWith("# ")) {
        sheet.getRange(i + 2, 8).setValue(line.replace("#", "").trim());
      }
    }
  }
}

************************************************************************************************

################ Extract Markdown Info: Prompt - Part 2  (extract headings FAQ + text) ###################

Create a JS script for google sheets to extract specific information from the Markdown text in the column A and splits it into separate columns in the sheet for that row.

The name of an active sheet: ZW import.

The first part of the content to be extracted is located between the row that begins with "##" and the row that begins with "## Frequently Asked Questions". The content of these rows consists of up to seven H2 Headings and respective text below each of them.

1st H2 heading should be pulled into the respective row in the column J.
2nd H2 heading should be pulled into the respective row in the column L.
3rd H2 heading should be pulled into the respective row in the column N.
4th H2 heading should be pulled into the respective row in the column P.
5th H2 heading should be pulled into the respective row in the column R.
6th H2 heading should be pulled into the respective row in the column T.
7th H2 heading should be pulled into the respective row in the column V.

Do not extract empty lines.

the text below the 1st H2 heading should be pulled into the respective row in the column K.
the text below the 2nd H2 heading should be pulled into the respective row in the column M.
the text below the 3rd H2 heading should be pulled into the respective row in the column O.
the text below the 4th H2 heading should be pulled into the respective row in the column Q.
the text below the 5th H2 heading should be pulled into the respective row in the column S.
the text below the 6th H2 heading should be pulled into the respective row in the column U.
the text below the 7th H2 heading should be pulled into the respective row in the column W.

Do not extract empty lines.

The next part of the content to be extracted is located after the row that begins with "## Frequently Asked Questions". The content of these rows consists of up to five H3 Headings and respective text below each of them.

1st H3 heading should be pulled into the respective row in the column X.
2nd H3 heading should be pulled into the respective row in the column Z.
3rd H3 heading should be pulled into the respective row in the column AB.
4th H3 heading should be pulled into the respective row in the column AD.
5th H3 heading should be pulled into the respective row in the column AF.

Do not extract empty lines.

the text below the 1st H2 heading should be pulled into the respective row in the column Y.
the text below the 2nd H2 heading should be pulled into the respective row in the column AA.
the text below the 3rd H2 heading should be pulled into the respective row in the column AC.
the text below the 4th H2 heading should be pulled into the respective row in the column AE.
the text below the 5th H2 heading should be pulled into the respective row in the column AF.

Do not extract empty lines.

#####################################################

**************** Extract Markdown Info: Script -  Part 2 (extract headings FAQ + text + NO EMPTY LINES) **********************
function extractFromMarkdown() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ZW import');
  var markdowns = sheet.getRange('A1:A' + sheet.getLastRow()).getValues();

  for (var i = 0; i < markdowns.length; i++) {
    var markdown = markdowns[i][0];

    var faqIndex = markdown.indexOf('## Frequently Asked Questions');
    var part1 = markdown.substring(0, faqIndex);
    var part2 = markdown.substring(faqIndex);

    var h2Sections = part1.split('\n## ').slice(1);
    for (var j = 0; j < h2Sections.length; j++) {
      var splitIndex = h2Sections[j].indexOf('\n');
      var title = h2Sections[j].substring(0, splitIndex).trim();
      var text = h2Sections[j].substring(splitIndex).trim().replace(/\n/g, ' ');

      sheet.getRange(i + 1, 'J'.charCodeAt(0) - 64 + j * 2).setValue(title);
      sheet.getRange(i + 1, 'K'.charCodeAt(0) - 64 + j * 2).setValue(text);
    }

    var h3Sections = part2.split('\n### ').slice(1);
    for (var j = 0; j < h3Sections.length; j++) {
      var splitIndex = h3Sections[j].indexOf('\n');
      var title = h3Sections[j].substring(0, splitIndex).trim();
      var text = h3Sections[j].substring(splitIndex).trim().replace(/\n/g, ' ');

      sheet.getRange(i + 1, 'X'.charCodeAt(0) - 64 + j * 2).setValue(title);
      sheet.getRange(i + 1, 'Y'.charCodeAt(0) - 64 + j * 2).setValue(text);
    }
  }
}







************************************************************************************************
################ Extract Markdown Info: Prompt - Part 3  (extract Intro text) ###################

Create a JS script for google sheets to extract specific information from the Markdown text in the column A and splits it into separate columns in the sheet for that row.

The name of an active sheet: ZW import.

The part of the content to be extracted is located between the row following the row that begins with "# " and the row that begins with "## ".
Do not extract the content of the row that begins with "# ".
Do not extract empty lines.

This extracted content should be pulled in the respective row in the the column I.

#####################################################

**************** Extract Markdown Info: Script -  Part 3 (extract Intro text) **********************
function processMarkdown() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ZW import");
  var data = sheet.getRange("A1:A" + sheet.getLastRow()).getValues();

  for (var i = 0; i < data.length; i++) {
    var text = data[i][0];
    var result = "";

    // match and extract the content
    var match = text.match(/#\s[\s\S]*?(?=\n##\s)/);
    if (match) {
      var lines = match[0].split('\n');
      for (var j = 1; j < lines.length; j++) { // start from 1 to exclude the line with "# "
        var line = lines[j].trim();
        if (line !== "") {
          result += line + '\n';
        }
      }
    }
    // write the result to the column I of the same row
    sheet.getRange(i + 1, 9).setValue(result);
  }
}


###############################################
************************************************************************************************
################ Convert to HTML: Prompt - Part 4  (Convert to HTML) ###################
Create a JS script for google sheets to:
1) Convert the text in the columns J,  L, N, P, R, T, V, X, Z, AB, AD, AF  into HTML markdown marked as H2 heading.

2) Convert the text in the columns K, M, O, Q, S, U, W, Y, AA, AC, AE, AG into HTML markdown marked as paragraphs.


#####################################################

**************** Convert to HTML: Prompt - Part 4  (Convert to HTML) **********************
function convertToMarkdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); // Gets the current active sheet.
  var range = sheet.getDataRange(); // Gets the range of the data.
  var values = range.getValues(); // Gets the values of the cells in the range.

  // Heading columns
  var headingColumns = ['J', 'L', 'N', 'P', 'R', 'T', 'V', 'X', 'Z', 'AB', 'AD', 'AF'];
  // Paragraph columns
  var paragraphColumns = ['K', 'M', 'O', 'Q', 'S', 'U', 'W', 'Y', 'AA', 'AC', 'AE', 'AG'];

  // Convert to markdown
  for (var row = 0; row < values.length; row++) {
    for (var col = 0; col < values[row].length; col++) {
      var columnName = columnToLetter(col+1); // +1 as col is 0 indexed

      // Check if the column is in the heading list, and if it contains any value
      if (headingColumns.includes(columnName) && values[row][col]) {
        // Convert the text in the cell to H2 heading in markdown
        values[row][col] = '<h2>' + values[row][col] + '</h2>';
      }
      // Check if the column is in the paragraph list, and if it contains any value
      else if (paragraphColumns.includes(columnName) && values[row][col]) {
        // Convert the text in the cell to paragraph in markdown
        values[row][col] = '<p>' + values[row][col] + '</p>';
      }
    }
  }

  // Apply the changes
  range.setValues(values);
}

// Function to convert column number to letter.
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}