0
Update Values
One script reply has been approved by the moderators Verified

This script updates values in a Google Sheet.

Created by adam-kov 520 days ago Viewed 117281 times
0
Submitted by adam-kov Deno
Verified 520 days ago
1
/** Update values(s) in the given range.
2
 *
3
 * @param startCell Use 'A1' notation.
4
 * https://developers.google.com/sheets/api/guides/concepts#cell
5
 *
6
 * @param responseValueRenderOption Determines how values in the response should be rendered.
7
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption
8
 *
9
 * @param responseDateTimeRenderOption Determines how dates, times, and durations in the response should be rendered.
10
 * This is ignored if `responseValueRenderOption` is `FORMATTED_VALUE`.
11
 * https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption
12
 */
13
type Gsheets = {
14
  token: string;
15
};
16
export async function main(
17
  gsheets_auth: Gsheets,
18
  spreadsheetId: string,
19
  startCell: string,
20
  values: Array<Array<any>>,
21
  majorDimension: "ROWS" | "COLUMNS" = "ROWS",
22
  valueInputOption: "RAW" | "USER_ENTERED" = "USER_ENTERED",
23
  responseValueRenderOption:
24
    | "FORMATTED_VALUE"
25
    | "UNFORMATTED_VALUE"
26
    | "FORMULA" = "FORMATTED_VALUE",
27
  responseDateTimeRenderOption:
28
    | "SERIAL_NUMBER"
29
    | "FORMATTED_STRING" = "SERIAL_NUMBER",
30
) {
31
  const token = gsheets_auth["token"];
32
  let UPDATE_VALUES_URL = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${startCell}?includeValuesInResponse=true`;
33
  UPDATE_VALUES_URL += `&valueInputOption=${valueInputOption}`;
34
  UPDATE_VALUES_URL += `&responseValueRenderOption=${responseValueRenderOption}`;
35
  UPDATE_VALUES_URL += `&responseDateTimeRenderOption=${responseDateTimeRenderOption}`;
36
  const body = {
37
    values,
38
    majorDimension,
39
  };
40

41
  const response = await fetch(UPDATE_VALUES_URL, {
42
    method: "PUT",
43
    body: JSON.stringify(body),
44
    headers: {
45
      Authorization: "Bearer " + token,
46
      "Content-Type": "application/json",
47
    },
48
  });
49

50
  const json = await response.json();
51
  if (json.error) {
52
    let { code, status, message } = json.error;
53
    throw Error(`\n${code} ${status} - "${message}"\n`);
54
  }
55
  return json;
56
}
57