logo Windmill Hub
Login
Back to Gsheets integration
0

Add Column

by
Published today

Add a column with a header after the last used column of a worksheet.

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 
/**
29 
 * Add Column
30 
 * Add a column titled `header` right after the last non-empty header cell of row 1, growing the grid when it is full. Returns the new column's letter.
31 
 */
32 
export async function main(
33 
  auth: RT.Gsheets,
34 
  spreadsheet_id: string,
35 
  sheet_name: DynSelect_sheet_name,
36 
  header: string
37 
) {
38 
  const headers = {
39 
    Authorization: `Bearer ${auth.token}`,
40 
    Accept: "application/json",
41 
  }
42 
  const base = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}`
43 
  const tab = `'${sheet_name.replaceAll("'", "''")}'`
44
45 
  const [metaRes, rowRes] = await Promise.all([
46 
    fetch(
47 
      `${base}?fields=sheets.properties(sheetId,title,gridProperties.columnCount)`,
48 
      { headers }
49 
    ),
50 
    fetch(`${base}/values/${encodeURIComponent(`${tab}!1:1`)}`, { headers }),
51 
  ])
52 
  for (const r of [metaRes, rowRes]) {
53 
    if (!r.ok) throw new Error(`${r.status} ${await r.text()}`)
54 
  }
55 
  const { sheets } = (await metaRes.json()) as {
56 
    sheets: {
57 
      properties: {
58 
        sheetId: number
59 
        title: string
60 
        gridProperties: { columnCount: number }
61 
      }
62 
    }[]
63 
  }
64 
  const sheet = sheets.find((s) => s.properties.title === sheet_name)
65 
  if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
66 
  const { values = [[]] } = (await rowRes.json()) as { values?: string[][] }
67 
  const index = values[0].length // 0-based index of the new column
68
69 
  if (index >= sheet.properties.gridProperties.columnCount) {
70 
    const grow = await fetch(`${base}:batchUpdate`, {
71 
      method: "POST",
72 
      headers: { ...headers, "Content-Type": "application/json" },
73 
      body: JSON.stringify({
74 
        requests: [
75 
          {
76 
            appendDimension: {
77 
              sheetId: sheet.properties.sheetId,
78 
              dimension: "COLUMNS",
79 
              length: 1,
80 
            },
81 
          },
82 
        ],
83 
      }),
84 
    })
85 
    if (!grow.ok) throw new Error(`${grow.status} ${await grow.text()}`)
86 
  }
87
88 
  let letter = ""
89 
  for (let n = index + 1; n > 0; n = Math.floor((n - 1) / 26))
90 
    letter = String.fromCharCode(65 + ((n - 1) % 26)) + letter
91
92 
  const url = new URL(
93 
    `${base}/values/${encodeURIComponent(`${tab}!${letter}1`)}`
94 
  )
95 
  url.searchParams.append("valueInputOption", "RAW")
96 
  const response = await fetch(url, {
97 
    method: "PUT",
98 
    headers: { ...headers, "Content-Type": "application/json" },
99 
    body: JSON.stringify({ values: [[header]] }),
100 
  })
101
102 
  if (!response.ok) {
103 
    throw new Error(`${response.status} ${await response.text()}`)
104 
  }
105
106 
  return { column: letter, ...(await response.json()) }
107 
}
108