AppsScriptPulse

Gotchas, V8, Docs, Drive

Retrieving Google Docs summaries using Google Apps Script (hint: it’s using the existing Google Drive description property) 

Tags:
| | 0 Comments |

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs. I’m sure this will catch some people out and hopefully this post will point people in the right direction.

Source: Retrieving Summary of Google Document using Google Apps Script

Solutions, Sheets, V8, Intermediate

Using spread syntax and destructuring assignment in Google Sheets .getValues() with Google Apps Script

Tags:
| | 0 Comments |

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. … I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.

We’ve featured other coding patterns from Kanshi Tanaike and here is some other nice examples of how you can handle 2D arrays returned by .getValues(), in particular, this post shows a nice way of getting non-adjacent columns in Google Sheets.

Source: Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

Solutions, Sheets, V8, Intermediate

Converting Google Sheets cell values to an object array with heading keys using Google Apps Script

Tags:
| | 2 Comments |

This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.

I have a minor obsession with methods for converting Google Sheets data to an object array. Over the years I’ve documented various approaches I’ve developed on my personal site, like this one, but this solution from Kanshi Tanaike has to be my new favourite:

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getDataRange()
    .getValues();
  const res = rows.map((r) =>
    headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})
  );
  console.log(res);
}

Source: Converting Values of Google Spreadsheet to Object using Google Apps Script

Solutions, Sheets, V8, Intermediate

Importing CSV data by keeping number formats of cells on Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.

When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.

When the following sample script of Google Apps Script is run, a dialog is opened. And, when the CSV file is selected from your local PC on the dialog, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats of cells.

The quoted post description explains everything you need about this solution and worth clicking through to see how the code works.

Source: Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script

Solutions, Forms, Beginner

Using Google Forms API with Google Apps Script

Tags:
| | 0 Comments |

The Google Forms API is currently in Open Beta. For Google Apps Script users the new Forms API is worth keeping an eye on as it enables functionality not currently included in the native FormsApp service. In this post from Kanshi Tanaike an example is looked at where the Forms API is used to insert an image to a question. If you are interested in trying this example out remember that you currently need to apply for access at the Early Adopter Program page.

Source: Using Google Forms API with Google Apps Script

Solutions, Docs, Forms, Sheets, V8, Intermediate

Pseudo OnEdit Trigger for Google Document using Google Apps Script

Tags:
| | 2 Comments |

In the current stage, there is not OnEdit trigger for Google Document. But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Source: Pseudo OnEdit Trigger for Google Document using Google Apps Script

Solutions, Sheets, Web App, V8, Beginner

Letting users run a Google Apps Script on Google Sheets without authorizing scopes and showing the source code

Tags:
| | 0 Comments |

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The post highlights the use of the existing =IMPORTML() Google Sheets function and a publish webapp to run a Google Apps Script without the end user having to authorize the script. A limitation of this approach, and also important warning to keep in mind, is the deployed web app needs to be deployed to run as the script owner and by anyone who has the web app link.

Source: Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

Solutions, V8, Intermediate

Sending Outlook Emails using Microsoft Account with Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for sending Outlook emails using Microsoft account with Google Apps Script. Before you use this script, please install OnedriveApp which is Google Apps Script library. And, please authorize your Microsoft account for using Microsoft Graph API.

The author of this contribution, Kanshi Tanaike, always comes up with interesting Google Apps Script solutions. This particular example uses a OnedriveApp library which makes it easy to interact with other part of the Microsoft Graph API expanding the possibilities.

Source: Sending Outlook Emails using Microsoft Account with Google Apps Script

Solutions, Gmail, V8, Intermediate

Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for sending multiple emails using the batch request with Gmail API using Google Apps Script. When multiple emails are sent using “GmailApp.sendEmail” and “MailApp.sendEmail”, a loop is used. But in this case, the process cost becomes high. In this post, I would like to introduce the sample script for reducing the process cost under this situation. Gmail API can be requested with the batch request. The batch request can be processed with the asynchronous process. By this, I thought that the process cost for sending multiple emails. So, this sample script sends multiple emails using the batch request with Gmail API.

I imagine many in the Apps Script developer community have a script or two that sends email. This post highlights a solution using the Gmail API to send emails in a batch, keeping in mind the limitations highlighted in the post.

Source: Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

Gotchas, Solutions, V8, Intermediate

Executing Function with Minutes timer in Specific Times using Google Apps Script [and tips on creating triggers in V8]

Tags:
| | 0 Comments |

This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved: Execute a function every 10 minutes only in 09:00 – 12:00 and 15:00 – 18:00 for the weekday.

A clever bit of coding from Kanshi Tanaike for running a Google Apps Script on a defined schedule. The part I found particularly interesting was the solution for programmatically setting triggers in the V8 runtime:

This sample script used Web Apps. Because when v8 runtime is used, when the trigger is set from the function executing by a trigger, the trigger is disabled. This is the recognized bug. But unfortunately, this has still not been resolved. (September 21, 2021) But, when the trigger is installed by Web Apps, no issue occurs. So, please deploy Web Apps.

For the sample code and explanation visit the source link.

Source: Executing Function with Minutes timer in Specific Times using Google Apps Script