Editing article

Title

Summary

Content

<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*sqLiSi5cK6pT0Qbx.png" /></figure><h3>Abstract</h3><p>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.</p><h3>Introduction</h3><p>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.</p><p>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.</p><p>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.</p><h3>Usage</h3><p>In order to test this sample script, please do the following flow.</p><h3>1. Create a Google Spreadsheet</h3><p>Create a new Google Spreadsheet. And, put a sample values like the top demonstration image.</p><p>And, open the script editor.</p><h3>2. Sample script</h3><p>Please copy and paste the following script to the script editor of Spreadsheet. And, please enable Sheets API at Advanced Google services. <a href="https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services">Ref</a> Sheets API is used for putting the values to each cell.</p><p>In this script, the values of inputValues is appended to “Sheet1”.</p><pre>function myFunction() {<br>  // Sample input values.<br>  const inputValues = {<br>    head1: [&quot;sample1&quot;, &quot;sample1&quot;],<br>    head2: [&quot;sample2&quot;, &quot;sample2&quot;],<br>    head3: [&quot;sample3&quot;, &quot;sample3&quot;, &quot;sample3&quot;],<br>    head4: [&quot;sample4&quot;],<br>    head5: [&quot;sample5&quot;],<br>  };<br><br>  // Retrieve current values from the sheet.<br>  const sheetName = &quot;Sheet1&quot;; // Please set your sheet name.<br>  const ss = SpreadsheetApp.getActiveSpreadsheet();<br>  const sheet = ss.getSheetByName(sheetName);<br>  const currentValues = sheet.getDataRange().getDisplayValues();<br><br>  // Retrieve the last rows of each column.<br>  const transposed = UtlApp.transpose(currentValues);<br>  const lastRowObj = transposed.reduce(<br>    (o, [h, ...v], i) =&gt; (<br>      (o[h] = `${UtlApp.columnIndexToLetter(i)}${<br>        v.flatMap((e, j) =&gt; (e ? [j + 3] : [])).pop() || 2<br>      }`),<br>      o<br>    ),<br>    {}<br>  );<br><br>  // Create a request body for putting values to the sheet.<br>  const data = Object.entries(inputValues).reduce((ar, [k, v]) =&gt; {<br>    if (lastRowObj[k]) {<br>      ar.push({ range: lastRowObj[k], values: v.map((e) =&gt; [e]) });<br>    }<br>    return ar;<br>  }, []);<br><br>  // Put values using Sheets API.<br>  if (data.length == 0) return;<br>  Sheets.Spreadsheets.Values.batchUpdate(<br>    { data, valueInputOption: &quot;USER_ENTERED&quot; },<br>    ss.getId()<br>  );<br>}</pre><h3>3. Testing</h3><p>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.</p><h3>Additional information</h3><p>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.</p><p>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:</p><pre>Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: &quot;USER_ENTERED&quot; }, ss.getId());<br><br>// OR<br><br>Sheets.Spreadsheets.Values.batchUpdate({ data, value_input_option: &quot;USER_ENTERED&quot; }, ss.getId());</pre><p>In this example, both valueInputOption and value_input_option are accepted by the API and will function identically.</p><h3>References</h3><ul><li><a href="https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object)">appendRow(rowContents) of Class Sheet</a></li><li><a href="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate">Method: spreadsheets.values.batchUpdate</a></li><li>I proposed this method to <a href="https://stackoverflow.com/q/78251515">this thread on Stackoverflow</a> as Python script.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8fa43d026e1b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/technique-for-appending-values-to-specific-columns-on-google-spreadsheet-using-google-apps-script-8fa43d026e1b">Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>

Author

Link

Published date

Image url

Feed url

Guid

Hidden blurb

--- !ruby/object:Feedjira::Parser::RSSEntry
title: Technique for Appending Values to Specific Columns on Google Spreadsheet using
  Google Apps Script
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
author: Kanshi Tanaike
categories:
- google-cloud-platform
- google-sheets
- google-apps-script
- google-spreadsheets
- gcp-app-dev
published: 2024-04-01 05:52:04.000000000 Z
entry_id: !ruby/object:Feedjira::Parser::GloballyUniqueIdentifier
  is_perma_link: 'false'
  guid: https://medium.com/p/8fa43d026e1b
carlessian_info:
  news_filer_version: 2
  newspaper: Google Cloud - Medium
  macro_region: Blogs
rss_fields:
- title
- url
- author
- categories
- published
- entry_id
- content
content: '<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*sqLiSi5cK6pT0Qbx.png"
  /></figure><h3>Abstract</h3><p>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.</p><h3>Introduction</h3><p>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.</p><p>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.</p><p>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.</p><h3>Usage</h3><p>In
  order to test this sample script, please do the following flow.</p><h3>1. Create
  a Google Spreadsheet</h3><p>Create a new Google Spreadsheet. And, put a sample values
  like the top demonstration image.</p><p>And, open the script editor.</p><h3>2. Sample script</h3><p>Please
  copy and paste the following script to the script editor of Spreadsheet. And, please
  enable Sheets API at Advanced Google services. <a href="https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services">Ref</a>
  Sheets API is used for putting the values to each cell.</p><p>In this script, the
  values of inputValues is appended to “Sheet1”.</p><pre>function myFunction() {<br>  //
  Sample input values.<br>  const inputValues = {<br>    head1: [&quot;sample1&quot;,
  &quot;sample1&quot;],<br>    head2: [&quot;sample2&quot;, &quot;sample2&quot;],<br>    head3:
  [&quot;sample3&quot;, &quot;sample3&quot;, &quot;sample3&quot;],<br>    head4: [&quot;sample4&quot;],<br>    head5:
  [&quot;sample5&quot;],<br>  };<br><br>  // Retrieve current values from the sheet.<br>  const
  sheetName = &quot;Sheet1&quot;; // Please set your sheet name.<br>  const ss = SpreadsheetApp.getActiveSpreadsheet();<br>  const
  sheet = ss.getSheetByName(sheetName);<br>  const currentValues = sheet.getDataRange().getDisplayValues();<br><br>  //
  Retrieve the last rows of each column.<br>  const transposed = UtlApp.transpose(currentValues);<br>  const
  lastRowObj = transposed.reduce(<br>    (o, [h, ...v], i) =&gt; (<br>      (o[h]
  = `${UtlApp.columnIndexToLetter(i)}${<br>        v.flatMap((e, j) =&gt; (e ? [j
  + 3] : [])).pop() || 2<br>      }`),<br>      o<br>    ),<br>    {}<br>  );<br><br>  //
  Create a request body for putting values to the sheet.<br>  const data = Object.entries(inputValues).reduce((ar,
  [k, v]) =&gt; {<br>    if (lastRowObj[k]) {<br>      ar.push({ range: lastRowObj[k],
  values: v.map((e) =&gt; [e]) });<br>    }<br>    return ar;<br>  }, []);<br><br>  //
  Put values using Sheets API.<br>  if (data.length == 0) return;<br>  Sheets.Spreadsheets.Values.batchUpdate(<br>    {
  data, valueInputOption: &quot;USER_ENTERED&quot; },<br>    ss.getId()<br>  );<br>}</pre><h3>3.
  Testing</h3><p>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.</p><h3>Additional information</h3><p>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.</p><p>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:</p><pre>Sheets.Spreadsheets.Values.batchUpdate({
  data, valueInputOption: &quot;USER_ENTERED&quot; }, ss.getId());<br><br>// OR<br><br>Sheets.Spreadsheets.Values.batchUpdate({
  data, value_input_option: &quot;USER_ENTERED&quot; }, ss.getId());</pre><p>In this
  example, both valueInputOption and value_input_option are accepted by the API and
  will function identically.</p><h3>References</h3><ul><li><a href="https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object)">appendRow(rowContents)
  of Class Sheet</a></li><li><a href="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate">Method:
  spreadsheets.values.batchUpdate</a></li><li>I proposed this method to <a href="https://stackoverflow.com/q/78251515">this
  thread on Stackoverflow</a> as Python script.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8fa43d026e1b"
  width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/technique-for-appending-values-to-specific-columns-on-google-spreadsheet-using-google-apps-script-8fa43d026e1b">Technique
  for Appending Values to Specific Columns on Google Spreadsheet using Google Apps
  Script</a> was originally published in <a href="https://medium.com/google-cloud">Google
  Cloud - Community</a> on Medium, where people are continuing the conversation by
  highlighting and responding to this story.</p>'

Language

Active

Ricc internal notes

Ricc source

Show this article Back to articles