Skip to content

Instantly share code, notes, and snippets.

@ndthanh
ndthanh / excelwebservice.vb
Created Jun 17, 2021 — forked from seamusabshere/excelwebservice.vb
Excel VB for accessing http://carbon.brighterplanet.com/flights.txt as a web service (early 2011)
View excelwebservice.vb
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
ndthanh / submit.md
Created Jun 16, 2021 — forked from tanaikech/submit.md
Batching Requests for Google Apps Script
View submit.md

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
ndthanh / submit.md
Created Jun 16, 2021 — forked from tanaikech/submit.md
Straightening Elements in 2 Dimensional Array using Google Apps Script
View submit.md

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
ndthanh / submit.md
Created Jun 16, 2021 — forked from tanaikech/submit.md
Changing Values by Checking Duplicated Values of JSON for Javascript
View submit.md

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
ndthanh / submit.md
Created Jun 16, 2021 — forked from tanaikech/submit.md
Multipart-POST Request Using Google Apps Script
View submit.md

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
ndthanh / submit.md
Created Jun 16, 2021 — forked from tanaikech/submit.md
OCR using Google Drive API
View submit.md

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
ndthanh / vEquivalents.gs
Created Sep 28, 2020 — forked from brucemcpherson/vEquivalents.gs
Google Apps Script equivalents for common VBA function
View vEquivalents.gs
/** @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
View extract_numbers.txt
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"
View onEdit_add_time.js
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) { // Thay tên sheet tương ứng
var r = s.getActiveCell();
if( r.getColumn() == 1 ) { // kiểm tra cột đang nhập dữ liệu phải cột thứ 1 hay không
var nextCell = r.offset(0, 1); // dùng offset để ghi thời gian sang cột bên phải
if( nextCell.getValue() === '' ) // kiểm tra xem ô kế tiếp có rỗng không
nextCell.setValue(new Date());
}
}
View unpivot.gs
/**
* Unpivots the given data
* More: https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets
*
* @return Unpivoted data from array
* @param {object[][]} arr 2D Input Array
* @param {object[][]=} headers [optional] Custom headers for output
* @customfunction
*/
function unpivot(arr, headers) {