"title"=>"Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script",
"summary"=>nil,
"content"=>"
![\"\"](\"https://cdn-images-1.medium.com/max/1000/0*sqLiSi5cK6pT0Qbx.png\")
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.
References
- appendRow(rowContents) of Class Sheet
- Method: spreadsheets.values.batchUpdate
- I proposed this method to this thread on Stackoverflow as Python script.
Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
","author"=>"Kanshi Tanaike",
"link"=>"https://medium.com/google-cloud/technique-for-appending-values-to-specific-columns-on-google-spreadsheet-using-google-apps-script-8fa43d026e1b?source=rss----e52cf94d98af---4",
"published_date"=>Mon, 01 Apr 2024 05:52:04.000000000 UTC +00:00,
"image_url"=>nil,
"feed_url"=>"https://medium.com/google-cloud/technique-for-appending-values-to-specific-columns-on-google-spreadsheet-using-google-apps-script-8fa43d026e1b?source=rss----e52cf94d98af---4",
"language"=>nil,
"active"=>true,
"ricc_source"=>"feedjira::v1",
"created_at"=>Mon, 01 Apr 2024 06:40:13.279679000 UTC +00:00,
"updated_at"=>Mon, 21 Oct 2024 17:09:10.500856000 UTC +00:00,
"newspaper"=>"Google Cloud - Medium",
"macro_region"=>"Blogs"}