时间戳

首页 > 解决方案 > 当列中的单元格值更改时如何插入行

google-apps-script - 当列中的单元格值更改时如何插入行

问题描述

当单元格值与列中单元格上方的单元格值不同时，如何插入与第一行的值相等的行H:H

在此处输入图像描述

标签： google-apps-scriptgoogle-sheetsinsert

解决方案

I believe your goal as follows.

  • You want to insert a new row and put the header row to the inserted row using Google Apps Script.

  • In your question, you want to insert the new row when the value of column "F" is changed. But when I saw your sample image, the column is "H" instead of "F".

    • Unfortunately, I cannot understand about your actual goal. So in this answer, I would like to propose 2 patterns.

  • From your following replying,

    The script is perfect. Can you modify the Text Color is RED and Fill Color is Yellow like a sample.

    • You want to put the values with the red font color and the yellow background color.

Sample script:

Before you use this script, please set the sheet name. And, in this sample script, the values of column "F" are checked. When you want to check the values of column "H", please modify "F2:F" to "H2:H".

function myFunction() {
  const sheetName = "Sheet1";  // Please set the sheetname.
  
  // 1. Retrieve values from the column "F".
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let [temp, ...values] = sheet.getRange("F2:F" + sheet.getLastRow()).getValues();
  
  // 2. Create an array including the row numbers for inserting new rows.
  const insertRows = values.reduce((ar, [v], i) => {
    if (v != temp) {
      ar.push(i + 3);
      temp = v;
    }
    return ar;
  }, []);

  // 3. Retrieve header row.
  const header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

  // 4. Insert new rows and put the header row to the inserted row.
  insertRows.reverse().forEach(i => {
    sheet.insertRows(i);
    sheet.getRange(i, 1, 1, header[0].length).setValues(header).setBackground("yellow").setFontColor("red"); // Modified
  });
}

Note:

  • This sample script is used for your question and the sample image. So when you changed the structure of your Spreadsheet, the script might not be able to be used. Please be careful this.

Reference:

推荐阅读