CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
KJCAD
KJCAD

Reputation: 1

Using Apps Script for Google Sheets, Insert a Header Row after change in cell value

I have not done any scripting for about 7 years. I used to write a lot of VBA scripts with SQL for Access and Excel. I am now trying to automate a Google sheet row insertion with formatting and a cell value edit in the new row.

I am hoping someone who can script with their eyes closed will help this old lady with a script to automate a volunteer task for an all volunteer food coop so that I can pass this task on to someone with less spreadsheet skill.

I have written the task in my own fudgy language and am hoping someone can translate it into the proper language and syntax. Here it is:

function (createReceivingSheet)

for each cell in range A2: A500

if right(this.cell, 6) != right(this.cell.-1, 6)
        insert.row.above(this.cell)
    format(new row) bold, underline, font:arial, 12pt
    merge (newrow.column1:column5)
    format (newrow.cell.column1) border:bottom
    case edit(newrow,cell.column1) 
    when original.cell = "02 GM *" then "GO MACRO",
    when original.cell = "000 *" then "PRODUCE"
    end
    End function

In other words I want to insert a formatted title row above each change in vendor where the vendor code is the first 6 characters of the cells in column A.

I need the script to iterate through Col A:

  1. compare each cell with the cell above
  2. if the first 6 characters of the current cell are not equal to the first 6 characters of the cell above, then insert a row above
  3. format the newly inserted row in bold, underline, 12 pt
  4. merge the first 5 columns in the newly inserted row
  5. format the merged cells in the newly inserted row with a bottom border
  6. populate the first cell (column A) of the newly inserted row with a value based on a set of case statements when the original cell = "X" or "Y" or "Z" etc.

I do not know if this is an appropriate question to ask on this forum. Please let me know if you can help or if this is too much to ask on this forum.

Upvotes: 0

Views: 1811

Answers (1)

adashrod
adashrod

Reputation: 475

I think this covers most of your pseudocode. At the bottom, you'll have to add the values that you want to put into the new cells.

This onOpen function adds a new menu after Help. Select a range then use the menu item to run partitionVendors.

There's good documentation here for when you need to add more features: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app?hl=en

const VENDOR_PREFIX_LENGTH = 6;
const MENU_TITLE = "Stack Overflow";

/**
 * Adds a menu to the spreadsheet when the file is opened
 */
function onOpen(e) {
    var ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(MENU_TITLE)
    menu.addItem("partition vendors", "partitionVendors")
        .addToUi();
}

function partitionVendors() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet();
    const range = SpreadsheetApp.getActiveRange();

    for (let r = range.getNumRows(); r > 1; r--) {
        const currentCellValue = range.getCell(r, 1).getValues()[0][0];
        const previousCellValue = range.getCell(r - 1, 1).getValues()[0][0];
        if (currentCellValue.slice(0, VENDOR_PREFIX_LENGTH) !== previousCellValue.slice(0, VENDOR_PREFIX_LENGTH)) {
            const newRowNum = range.getLastRow() - range.getNumRows() + r;
            sheet.insertRowBefore(newRowNum);
            // assuming the selection is in column A, merge columns A to E
            const newCellRange = sheet.getRange(`A${newRowNum}:E${newRowNum}`);
            newCellRange.merge()
                .setFontWeight("bold")
                .setFontLine("underline")
                .setFontFamily("Arial")
                .setFontSize(12)
                .setBorder(null, null, true, null, null, null);
            // populate values here
            if (currentCellValue === "value1") {
                newCellRange.setValue("header for value1");
            } // else if ...
        }
    }
}

Upvotes: 0

Related Questions