logo Windmill Hub
Login
Back to Gsheets integration
0

Merge Cells

by
Published today

Merge or unmerge a range of cells.

Script gsheets Verified
Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)
Verified 1 hour ago
All edits
Permalink
1 
//native
2
3 
export type DynSelect_sheet_name = string
4
5 
// Dropdown of the spreadsheet's worksheet (tab) names.
6 
export async function sheet_name(auth: RT.Gsheets, spreadsheet_id: string) {
7 
  const response = await fetch(
8 
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties.title`,
9 
    {
10 
      headers: {
11 
        Authorization: `Bearer ${auth.token}`,
12 
        Accept: "application/json",
13 
      },
14 
    }
15 
  )
16 
  if (!response.ok) {
17 
    throw new Error(`${response.status} ${await response.text()}`)
18 
  }
19 
  const { sheets } = (await response.json()) as {
20 
    sheets: { properties: { title: string } }[]
21 
  }
22 
  return sheets.map((s) => ({
23 
    value: s.properties.title,
24 
    label: s.properties.title,
25 
  }))
26 
}
27
28 
// A1 range without the tab name (A1:F1, B:B, 2:2, C3) → GridRange; omitted bounds stay open.
29 
function gridRange(sheetId: number, range: string) {
30 
  const m = range.toUpperCase().match(/^([A-Z]*)(\d*)(?::([A-Z]*)(\d*))?$/)
31 
  if (!m || range === "") throw new Error(`Invalid A1 range "${range}"`)
32 
  const [, c1, r1, c2 = c1, r2 = r1] = m
33 
  const col = (c: string) =>
34 
    [...c].reduce((n, ch) => n * 26 + ch.charCodeAt(0) - 64, 0)
35 
  const grid: { [key: string]: number } = { sheetId }
36 
  if (c1) grid.startColumnIndex = col(c1) - 1
37 
  if (c2) grid.endColumnIndex = col(c2)
38 
  if (r1) grid.startRowIndex = Number(r1) - 1
39 
  if (r2) grid.endRowIndex = Number(r2)
40 
  return grid
41 
}
42
43 
/**
44 
 * Merge Cells
45 
 * Merge a range (A1 notation without the tab name, e.g. `A1:D1`) into one cell, or per row/column; only the top-left value is kept. `UNMERGE` splits every merged cell in the range.
46 
 */
47 
export async function main(
48 
  auth: RT.Gsheets,
49 
  spreadsheet_id: string,
50 
  sheet_name: DynSelect_sheet_name,
51 
  range: string,
52 
  merge_type:
53 
    "MERGE_ALL" | "MERGE_ROWS" | "MERGE_COLUMNS" | "UNMERGE" = "MERGE_ALL"
54 
) {
55 
  const headers = {
56 
    Authorization: `Bearer ${auth.token}`,
57 
    Accept: "application/json",
58 
  }
59 
  const meta = await fetch(
60 
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties(sheetId,title)`,
61 
    { headers }
62 
  )
63 
  if (!meta.ok) {
64 
    throw new Error(`${meta.status} ${await meta.text()}`)
65 
  }
66 
  const { sheets } = (await meta.json()) as {
67 
    sheets: { properties: { sheetId: number; title: string } }[]
68 
  }
69 
  const sheet = sheets.find((s) => s.properties.title === sheet_name)
70 
  if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
71 
  const sheetId = sheet.properties.sheetId
72
73 
  const grid = gridRange(sheetId, range)
74 
  const requests = [
75 
    merge_type === "UNMERGE"
76 
      ? { unmergeCells: { range: grid } }
77 
      : { mergeCells: { range: grid, mergeType: merge_type } },
78 
  ]
79
80 
  const response = await fetch(
81 
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}:batchUpdate`,
82 
    {
83 
      method: "POST",
84 
      headers: { ...headers, "Content-Type": "application/json" },
85 
      body: JSON.stringify({ requests }),
86 
    }
87 
  )
88
89 
  if (!response.ok) {
90 
    throw new Error(`${response.status} ${await response.text()}`)
91 
  }
92
93 
  return await response.json()
94 
}
95