logo Windmill Hub
Login
Back to Gsheets integration
0

Insert Rows or Columns

by
Published today

Insert empty rows or columns at a position in 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 
 * Insert Rows or Columns
30 
 * Insert `count` empty rows or columns before `position`: a row number for rows, a column letter (or number) for columns. New cells copy the formatting of the row/column before them when `inherit_from_before` is set, otherwise of the one after.
31 
 */
32 
export async function main(
33 
  auth: RT.Gsheets,
34 
  spreadsheet_id: string,
35 
  sheet_name: DynSelect_sheet_name,
36 
  dimension: "ROWS" | "COLUMNS",
37 
  position: string,
38 
  count: number = 1,
39 
  inherit_from_before: boolean = false
40 
) {
41 
  const start = /^\d+$/.test(position)
42 
    ? Number(position)
43 
    : [...position.toUpperCase()].reduce(
44 
        (n, c) => n * 26 + c.charCodeAt(0) - 64,
45 
        0
46 
      )
47 
  if (!(start >= 1)) throw new Error(`Invalid position "${position}"`)
48
49 
  const headers = {
50 
    Authorization: `Bearer ${auth.token}`,
51 
    Accept: "application/json",
52 
  }
53 
  const meta = await fetch(
54 
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties(sheetId,title)`,
55 
    { headers }
56 
  )
57 
  if (!meta.ok) {
58 
    throw new Error(`${meta.status} ${await meta.text()}`)
59 
  }
60 
  const { sheets } = (await meta.json()) as {
61 
    sheets: { properties: { sheetId: number; title: string } }[]
62 
  }
63 
  const sheet = sheets.find((s) => s.properties.title === sheet_name)
64 
  if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
65 
  const sheetId = sheet.properties.sheetId
66
67 
  const requests = [
68 
    {
69 
      insertDimension: {
70 
        range: {
71 
          sheetId,
72 
          dimension,
73 
          startIndex: start - 1,
74 
          endIndex: start - 1 + count,
75 
        },
76 
        // The API rejects inheritFromBefore on the first row/column.
77 
        inheritFromBefore: inherit_from_before && start > 1,
78 
      },
79 
    },
80 
  ]
81
82 
  const response = await fetch(
83 
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}:batchUpdate`,
84 
    {
85 
      method: "POST",
86 
      headers: { ...headers, "Content-Type": "application/json" },
87 
      body: JSON.stringify({ requests }),
88 
    }
89 
  )
90
91 
  if (!response.ok) {
92 
    throw new Error(`${response.status} ${await response.text()}`)
93 
  }
94
95 
  return await response.json()
96 
}
97