Gists

I have already reported for showing the log to “Apps Script Dashboard” when it requests to the Web Apps. Ref In order to show the log to “Apps Script Dashboard” when it requests to the Web Apps, it is required to use the access token. But in the current stage, when the access token is used for XMLHttpRequest and fetch of Javascript in the request headers, the error related to CORS occurs. So, in this report, I would like to propose the workaround for resolving this issue.

Gists

These are the sample scripts for achieving the switching buttons for Google Spreadsheet using Google Apps Script. The management of images using Spreadsheet service is growing now. But, in the current stage, in order to achieve the switching buttons, it needs a little ingenuity. In this report, I would like to introduce 4 kinds of the switching buttons.

Pattern 1

In this pattern, the drawing is used as the button.

Gists

This is a report for retrieving the logs in Web Apps for Google Apps Script, when it requests to the Web Apps.

Experimental condition

1. Sample script for Web Apps

const doGet = (e) => {
  Logger.log(`GET method: ${JSON.stringify(e)}`);
  console.log(`GET method: ${JSON.stringify(e)}`);
  return ContentService.createTextOutput(
    JSON.stringify({ method: "GET", e: e })
  );
};
const doPost = (e) => {
  Logger.log(`POST method: ${JSON.stringify(e)}`);
  console.log(`POST method: ${JSON.stringify(e)}`);
  return ContentService.createTextOutput(
    JSON.stringify({ method: "POST", e: e })
  );
};

• This Web Apps is deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

2. Sample Google Apps Script project

1. Google Apps Script of standalone type WITHOUT linking Google Cloud Platform (GCP) Project

Gists

This is a sample script for directly using the request body of the string JSON for Google APIs with googleapis of golang.

At googleapis for golang, when Google API is used, it is required to create the request body like this sample script. I have several contacts for creating about such request body. I thought that such script might be a bit difficult for users. I thought that when the string JSON object is directly used for this, it might be useful. So I would like to introduce about this. When this was useful for your situation, I’m glad.

Gists

This is a sample script for converting the SVG image data to PNG image data using Google Apps Script.

Unfortunately, in the current stage, there are no methods for directly converting the SVG to PNG in Google Drive service. But it can be achieved by Drive API. The sample script is as follows.

Before you use this, please enable Drive API at Advanced Google services.

Sample script

function myFunction() {
  const svgFileId = "###"; // Please set the fileId of the SVG file.

  const url = Drive.Files.get(svgFileId).thumbnailLink.replace(
    "=s220",
    "=s1000"
  );
  const blob = UrlFetchApp.fetch(url).getBlob(); // blob is the image blob of PNG format.

  // In this sample, the retrieved image blob is put to Spreadsheet.
  const sheet = SpreadsheetApp.openById("###").getSheetByName("Sheet1");
  sheet.insertImage(blob, 1, 1).setWidth(500).setHeight(500);
}

• In this sample script, the converted PNG image is put to the Spreadsheet.

Gists

This is a sample script for setting the number format of cells on Google Spreadsheet using batchUpdate in Sheets API with golang. In this case, googleapis for golang is used. The script of the authorization can be seen at the official document.

Sample script

In this script, the number format of the column “A” is changed to yyyy-mm-dd hh:mm:ss. And, please include https://www.googleapis.com/auth/spreadsheets to the scopes.

sheetId := 12345678  // Please set the sheet ID which is not Spreadsheet ID. Please be careful this.

repeatCellRequest := &sheets.RepeatCellRequest{
    Fields: "userEnteredFormat.numberFormat",
    Range: &sheets.GridRange{
        SheetId:          int64(sheetId),
        StartRowIndex:    0,
        StartColumnIndex: 0,
        EndColumnIndex:   1,
    },
    Cell: &sheets.CellData{
        UserEnteredFormat: &sheets.CellFormat{
            NumberFormat: &sheets.NumberFormat{
                Pattern: "yyyy-mm-dd hh:mm:ss",
                Type:    "DATE",
            },
        },
    },
}
requestBody := &sheets.BatchUpdateSpreadsheetRequest{
    Requests: []*sheets.Request{&sheets.Request{
        RepeatCell: repeatCellRequest,
    }},
}
resp, err := srv.Spreadsheets.BatchUpdate(bookID, requestBody).Do()
if err != nil {
    log.Fatal(err)
}
fmt.Printf("%#v\n", resp)

References

• Method: spreadsheets.batchUpdate
• RepeatCellRequest
• How to format a cell with Google Sheets API v4 and Go?
  • I answered above sample script at this thread.

Gists

This is the statistics for the tag triggers on Stackoverflow. The tag of triggers was created at 2011-07-28. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-16.

This statistics data was obtained at 2020-07-16.

Measurement result

Fig. 1: About triggers tag. Year vs. Total questions, Answered, Solved and Closed questions

Fig. 2: About google-apps-script tag and triggers tag. Year vs. Total questions, Answered, Solved and Closed questions

Gists

This is a sample script for retrieving “Users”, “Sessions” and “PageViews” of User Summary Report from Google Analytics using Google Apps Script. When you use this, please enable Analytics Reporting API at Advanced Google services.

Sample script

function myFunction() {
  const viewId = "###";
  const startDate = "2020-01-01";
  const endDate = "2020-06-01";

  const resource = {
    reportRequests: [
      {
        viewId: viewId,
        dateRanges: [{ startDate: startDate, endDate: endDate }],
        metrics: [
          { expression: "ga:users" },
          { expression: "ga:sessions" },
          { expression: "ga:pageviews" },
        ],
      },
    ],
  };
  const res = AnalyticsReporting.Reports.batchGet(resource, { fields: "*" });
  console.log(res);
}

Result

{
  "reports": [
    {
      "columnHeader": {
        "metricHeader": {
          "metricHeaderEntries": [
            {
              "name": "ga:users",
              "type": "INTEGER"
            },
            {
              "type": "INTEGER",
              "name": "ga:sessions"
            },
            {
              "type": "INTEGER",
              "name": "ga:pageviews"
            }
          ]
        }
      },
      "data": {
        "maximums": [
          {
            "values": ["###", "###", "###"]
          }
        ],
        "rowCount": 1,
        "totals": [
          {
            "values": ["###", "###", "###"]
          }
        ],
        "minimums": [
          {
            "values": ["###", "###", "###"]
          }
        ],
        "isDataGolden": true,
        "rows": [
          {
            "metrics": [
              {
                "values": ["###", "###", "###"]
              }
            ]
          }
        ]
      }
    }
  ]
}

Reference

• Analytics Reporting API

Gists

This table shows all tags using together with the tag of “google-apps-script” at Stackoverflow. The tags are summarized every year. At 2020, the data is retrieved from January 1, 2020 to July 16, 2020. The number enclosed (###) means the number of use of the tag. When this table was a material for discussing about the current tags for “google-apps-script”, I’m glad.

Gists

These are the sample scripts by the various languages for requesting to Web Apps created by Google Apps Script.

1. curl
2. Google Apps Script
3. Javascript
4. ajax
5. Node.js
6. axios
7. angular
8. go
9. python
10. php
11. powershell

Sample script for Web Apps

• Sample script for Web Apps is as follows.

  const doGet = (e) =>
    ContentService.createTextOutput(
      JSON.stringify({ method: "GET", eventObject: e })
    ).setMimeType(ContentService.MimeType.JSON);
  const doPost = (e) =>
    ContentService.createTextOutput(
      JSON.stringify({ method: "POST", eventObject: e })
    ).setMimeType(ContentService.MimeType.JSON);
  

• Setting for Web Apps is as follows.