スプレッドシートをプログラムから操作

addChart.json

{
  "requests": [
    {
      "addChart": {
        "chart": {
          "spec": {
            "title": "国語、算数、理科",
            "basicChart": {
              "chartType": "COLUMN",
              "legendPosition": "RIGHT_LEGEND",
              "axis": [
                {
                  "position": "BOTTOM_AXIS"
                },
                {
                  "position": "LEFT_AXIS"
                }
              ],
              "domains": [
                {
                  "domain": {
                    "sourceRange": {
                      "sources": [
                        {
                          "startRowIndex": 0,
                          "endRowIndex": 4,
                          "startColumnIndex": 0,
                          "endColumnIndex": 1
                        }
                      ]
                    }
                  }
                }
              ],
              "series": [
                {
                  "series": {
                    "sourceRange": {
                      "sources": [
                        {
                          "startRowIndex": 0,
                          "endRowIndex": 4,
                          "startColumnIndex": 1,
                          "endColumnIndex": 2
                        }
                      ]
                    }
                  },
                  "targetAxis": "LEFT_AXIS"
                },
                {
                  "series": {
                    "sourceRange": {
                      "sources": [
                        {
                          "startRowIndex": 0,
                          "endRowIndex": 4,
                          "startColumnIndex": 2,
                          "endColumnIndex": 3
                        }
                      ]
                    }
                  },
                  "targetAxis": "LEFT_AXIS"
                },
                {
                  "series": {
                    "sourceRange": {
                      "sources": [
                        {
                          "startRowIndex": 0,
                          "endRowIndex": 4,
                          "startColumnIndex": 3,
                          "endColumnIndex": 4
                        }
                      ]
                    }
                  },
                  "targetAxis": "LEFT_AXIS"
                }
              ],
              "headerCount": 1
            },
            "hiddenDimensionStrategy": "SKIP_HIDDEN_ROWS_AND_COLUMNS"
          },
          "position": {
            "overlayPosition": {
              "anchorCell": {
                "columnIndex": 5
              },
              "offsetXPixels": 34,
              "offsetYPixels": 15,
              "widthPixels": 470,
              "heightPixels": 290
            }
          }
        }
      }
    }
  ]
}

addConditionalFormatRule.json

{
  "requests": [
    {
      "addConditionalFormatRule": {
        "rule": {
          "ranges": [
            {
              "sheetId": 0,
              "startRowIndex": 1,
              "endRowIndex": 4,
              "startColumnIndex": 1,
              "endColumnIndex": 4
            }
          ],
          "booleanRule": {
            "condition": {
              "type": "NUMBER_LESS_THAN_EQ",
              "values": [
                {
                  "userEnteredValue": "50"
                }
              ]
            },
            "format": {
              "backgroundColor": {
                "red": 0.95686275,
                "green": 0.78039217,
                "blue": 0.7647059
              }
            }
          }
        }
      }
    }
  ]
}

updateBorders.json

{
  "requests": [
    {
      "updateBorders": {
        "range": {
          "sheetId": 0,
          "startRowIndex": 0,
          "endRowIndex": 4,
          "startColumnIndex": 0,
          "endColumnIndex": 5
        },
        "top": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }
        },
        "bottom": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }
        },
        "left": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }
        },
        "right": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }
        },
        "innerHorizontal": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }        
        },
        "innerVertical": {
          "style": "SOLID",
          "width": 1,
          "color": {
            "red": 0,
            "green": 0,
            "blue": 0,
            "alpha": 0
          }   
        }
      }
    }
  ]
}

updateCells.json

{
  "requests": [
    {
      "updateCells": {
        "start": {
            "sheetId": 0,
            "rowIndex": 0,
            "columnIndex": 0
        },
        "rows": [
          {
            "values": [
              {},
              {
                "userEnteredValue": {
                  "stringValue": "国語"
                }
              },
              {
                "userEnteredValue": {
                  "stringValue": "算数"
                }
              },
              {
                "userEnteredValue": {
                  "stringValue": "理科"
                }
              },
              {
                "userEnteredValue": {
                  "stringValue": "合計"
                }
              }
            ],
          },
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "A"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "80"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "70"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "60"
                }
              },
              {
                "userEnteredValue": {
                  "formulaValue": "=SUM(B2:D2)"
                }
              }
            ]
          },
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "B"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "90"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "40"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "50"
                }
              },
              {
                "userEnteredValue": {
                  "formulaValue": "=SUM(B3:D3)"
                }
              }
            ]
          },
          {
            "values": [
              {
                "userEnteredValue": {
                  "stringValue": "C"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "30"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "90"
                }
              },
              {
                "userEnteredValue": {
                  "numberValue": "70"
                }
              },
              {
                "userEnteredValue": {
                  "formulaValue": "=SUM(B4:D4)"
                }
              }
            ]
          }
        ],
        "fields": "userEnteredValue"
      }
    }
  ]
}