ndthanh

ndthanh

' replace only the LAST occurrence of a the SEARCH substring in SOURCE 
' with the REPLACESTR string - search isn't case-sensitive
ReplaceLast = StrReverse(Replace(StrReverse(Source), StrReverse(Search), StrReverse(ReplaceStr), , 1))

ndthanh

Option Explicit

Function GetBrighterPlanetApiKey()
    GetBrighterPlanetApiKey = ActiveWorkbook.Worksheets("Setup").Range("C2").Value
End Function

Function IsEmissionEstimateServiceOnline()
    If LCase(ActiveWorkbook.Worksheets("Setup").Range("C3").Value) = "online" And ThisWorkbook.HasFinishedWorkbookOpen() = True Then
        IsEmissionEstimateServiceOnline = True
    Else

ndthanh

Batching Requests for Google Apps Script

There is the bathing requests in the Google APIs. The bathing requests can use the several API calls as a single HTTP request. By using this, for example, users can modify filenames of a lot of files on Google Drive. But there are limitations for the number of API calls which can process in one batch request. For example, Drive API can be used the maximum of 100 calls in one batch request.

The following sample script modifies the filenames of 2 files on own Google Drive using Google Apps Script. Drive API for modifying filenames is used as "PATCH". The batch request including 2 API calls of Drive API is called using "multipart/mixed". If you want more APIs, please add the elements to the array of "body".

function main() {
  var body = [
    {
 method: "PATCH",

ndthanh

Straightening Elements in 2 Dimensional Array using Google Apps Script

This sample script is for straightening elements in 2 dimensional array using Google Apps Script (GAS). When applications using Spreadsheet are developed by GAS, it usually uses 2 dimensional array by setValues(). And the lengths of each element are required to be the same. On the other hand, data used for the applications might not be the same length for each element in 2 dimensional array. This sample script can be used under such situation.

Sample 1 :

var data = [
  ["string"],
  ["string", "string", "string", "string", "string"],
  ["string", "string", "string"],
 ["string", "string", "string", "string", "string"],

ndthanh

Changing Values by Checking Duplicated Values of JSON for Javascript

This sample script is for changing values by checking duplicated values of JSON for Javascript.

Please see the following script. There is an array with a JSON data with 3 keys and 3 values. It is found that the values for each element duplicate. These duplicated values are changing by adding numbers.

I use this for managing filenames. This script also can be used for Google Apps Script. If this was useful for you, I'm glad.

Script :

var data = [

ndthanh

Multipart-POST Request Using Google Apps Script

April 20, 2019: GAS library for this situation was published. Please check it at https://github.com/tanaikech/FetchApp.

These sample scripts are for requesting multipart post using Google Apps Script.

In most cases, the multipart request is used for uploading files. So I prepared 2 sample situations as follows. For each situation, the request parameters are different.

1. Upload a file from Google Drive to Slack.
2. Convert an excel file to Spreadsheet on Google Drive using Drive API v3.

ndthanh

OCR using Google Drive API

This is a sample script for OCR using Google Drive API. A text file which converted by OCR can be retrieved by inputting an image file.

In this sample, Python Quickstart is used. The detail information is https://developers.google.com/drive/v3/web/quickstart/python. Please read “Step 1: Turn on the Drive API” and “Step 2: Install the Google Client Library”.

from __future__ import print_function
import httplib2
import os

ndthanh

/** @description
 * javaScript/Google Apps script functions that are equivalent to common VBA functions
 * in general these provide the same functionality and have the same calling stack
 * See http://ramblings.mcpher.com/Home/excelquirks/codeuse for more details
 * @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="http://ramblings.mcpher.com"> ramblings.mcpher.com</a>
 */

/**
 * Removes leading and trailing whitespace
 * @param {string|number} v the item to be trimmed 

ndthanh

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mix", type text}}),
    #"Added Custom" = Table.AddColumn(
#"Changed Type", 
"Custom", 
each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),
each if Value.Is(Value.FromText(_), type number) then _ else null))))
in
    #"Added Custom"