Ctrl K

Delete Rows

Published today

Delete a range of rows from a worksheet, shifting the rows below up.

Script gsheets Verified

Use in Windmill

The script

Submitted by hugo989 Typescript (fetch-only)

Verified 1 hour ago

All edits

Permalink

//native

export type DynSelect_sheet_name = string

// Dropdown of the spreadsheet's worksheet (tab) names.
export async function sheet_name(auth: RT.Gsheets, spreadsheet_id: string) {
  const response = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties.title`,
    {
      headers: {
        Authorization: `Bearer ${auth.token}`,
        Accept: "application/json",
      },
    }
  )
  if (!response.ok) {
    throw new Error(`${response.status} ${await response.text()}`)
  }
  const { sheets } = (await response.json()) as {
    sheets: { properties: { title: string } }[]
  }
  return sheets.map((s) => ({
    value: s.properties.title,
    label: s.properties.title,
  }))
}

/**
 * Delete Rows
 * Delete rows `start_row` to `end_row` (1-based, inclusive; leave `end_row` empty to delete one row). Rows below shift up, so delete from the bottom when removing several separate rows.
 */
export async function main(
  auth: RT.Gsheets,
  spreadsheet_id: string,
  sheet_name: DynSelect_sheet_name,
  start_row: number,
  end_row: number | undefined
) {
  const headers = {
    Authorization: `Bearer ${auth.token}`,
    Accept: "application/json",
  }
  const meta = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties(sheetId,title)`,
    { headers }
  )
  if (!meta.ok) {
    throw new Error(`${meta.status} ${await meta.text()}`)
  }
  const { sheets } = (await meta.json()) as {
    sheets: { properties: { sheetId: number; title: string } }[]
  }
  const sheet = sheets.find((s) => s.properties.title === sheet_name)
  if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
  const sheetId = sheet.properties.sheetId

  const requests = [
    {
      deleteDimension: {
        range: {
          sheetId,
          dimension: "ROWS",
          startIndex: start_row - 1,
          endIndex: end_row ?? start_row,
        },
      },
    },
  ]

  const response = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}:batchUpdate`,
    {
      method: "POST",
      headers: { ...headers, "Content-Type": "application/json" },
      body: JSON.stringify({ requests }),
    }
  )

  if (!response.ok) {
    throw new Error(`${response.status} ${await response.text()}`)
  }

  return await response.json()
}


`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`	`* Delete Rows`
`30`	* Delete rows `start_row` to `end_row` (1-based, inclusive; leave `end_row` empty to delete one row). Rows below shift up, so delete from the bottom when removing several separate rows.
`31`	`*/`
`32`	`export async function main(`
`33`	`auth: RT.Gsheets,`
`34`	`spreadsheet_id: string,`
`35`	`sheet_name: DynSelect_sheet_name,`
`36`	`start_row: number,`
`37`	`end_row: number \| undefined`
`38`	`) {`
`39`	`const headers = {`
`40`	Authorization: `Bearer ${auth.token}`,
`41`	`Accept: "application/json",`
`42`	`}`
`43`	`const meta = await fetch(`
`44`	`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}?fields=sheets.properties(sheetId,title)`,
`45`	`{ headers }`
`46`	`)`
`47`	`if (!meta.ok) {`
`48`	throw new Error(`${meta.status} ${await meta.text()}`)
`49`	`}`
`50`	`const { sheets } = (await meta.json()) as {`
`51`	`sheets: { properties: { sheetId: number; title: string } }[]`
`52`	`}`
`53`	`const sheet = sheets.find((s) => s.properties.title === sheet_name)`
`54`	if (!sheet) throw new Error(`Worksheet "${sheet_name}" not found`)
`55`	`const sheetId = sheet.properties.sheetId`
`56`
`57`	`const requests = [`
`58`	`{`
`59`	`deleteDimension: {`
`60`	`range: {`
`61`	`sheetId,`
`62`	`dimension: "ROWS",`
`63`	`startIndex: start_row - 1,`
`64`	`endIndex: end_row ?? start_row,`
`65`	`},`
`66`	`},`
`67`	`},`
`68`	`]`
`69`
`70`	`const response = await fetch(`
`71`	`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheet_id}:batchUpdate`,
`72`	`{`
`73`	`method: "POST",`
`74`	`headers: { ...headers, "Content-Type": "application/json" },`
`75`	`body: JSON.stringify({ requests }),`
`76`	`}`
`77`	`)`
`78`
`79`	`if (!response.ok) {`
`80`	throw new Error(`${response.status} ${await response.text()}`)
`81`	`}`
`82`
`83`	`return await response.json()`
`84`	`}`
`85`