Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script

submit.md

Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script

Abstract

This report addresses the challenge of appending values to specific columns in Google Sheets when columns have uneven last rows. It offers a Google Apps Script solution with a sample script and demonstration image, enabling efficient and flexible data manipulation.

Introduction

Google Apps Script is a versatile tool that allows for seamless management of various Google Workspace applications, including Docs, Sheets, Slides, Forms, and APIs. Its ability to automate tasks within Google Sheets is particularly powerful.

There might be a scenario where you need to append values to specific columns in a Google Spreadsheet using Google Apps Script. While the Google Spreadsheet service (SpreadsheetApp) offers the appendRow method of the Sheet class to append values to the sheet, this method isn't suitable when the last rows of each column differ.

This report presents a sample script that overcomes this limitation, enabling you to append values to specific columns regardless of their individual last rows. You can refer to the demonstration image at the beginning of the report.

Usage

In order to test this sample script, please do the following flow.

1. Create a Google Spreadsheet

Create a new Google Spreadsheet. And, put a sample values like the top demonstration image.

And, open the script editor.

2. Sample script

Please copy and paste the following script to the script editor of Spreadsheet. And, please enable Sheets API at Advanced Google services. Ref Sheets API is used for putting the values to each cell.

In this script, the values of inputValues is appended to "Sheet1".

function myFunction() {
  // Sample input values.
  const inputValues = {
    head1: ["sample1", "sample1"],
    head2: ["sample2", "sample2"],
    head3: ["sample3", "sample3", "sample3"],
    head4: ["sample4"],
    head5: ["sample5"],
  };

  // Retrieve current values from the sheet.
  const sheetName = "Sheet1"; // Please set your sheet name.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const currentValues = sheet.getDataRange().getDisplayValues();

  // Retrieve the last rows of each column.
  const transposed = UtlApp.transpose(currentValues);
  const lastRowObj = transposed.reduce(
    (o, [h, ...v], i) => (
      (o[h] = `${UtlApp.columnIndexToLetter(i)}${
        v.flatMap((e, j) => (e ? [j + 3] : [])).pop() || 2
      }`),
      o
    ),
    {}
  );

  // Create a request body for putting values to the sheet.
  const data = Object.entries(inputValues).reduce((ar, [k, v]) => {
    if (lastRowObj[k]) {
      ar.push({ range: lastRowObj[k], values: v.map((e) => [e]) });
    }
    return ar;
  }, []);

  // Put values using Sheets API.
  if (data.length == 0) return;
  Sheets.Spreadsheets.Values.batchUpdate(
    { data, valueInputOption: "USER_ENTERED" },
    ss.getId()
  );
}

3. Testing

You can see the input and output situations by this script at the top demonstration image. The values of inputValues are appended. You can see it as the blue background color in the image.

Additional information

Recently, I learned from a Google developer that Google APIs offer flexibility in how you format parameter names within requests. Currently, you can use both camelCase and snake_case for parameter names in many Google APIs. This means you have more freedom when writing code and can choose the style that best suits your preference or coding conventions.

For example, in the above script, you can use either valueInputOption or value_input_option when making a batch update request to the Google Sheets API:

Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());

// OR

Sheets.Spreadsheets.Values.batchUpdate({ data, value_input_option: "USER_ENTERED" }, ss.getId());

In this example, both valueInputOption and value_input_option are accepted by the API and will function identically.

References

• appendRow(rowContents) of Class Sheet
• Method: spreadsheets.values.batchUpdate
• I proposed this method to this thread on Stackoverflow as Python script.