work-in-progress-google-apps-sheets-mastodon.js

function postToMastodon(event) {
  try {
    if (event) {
      const range = event.range;

      console.log(
        `cell in column ${range.columnStart} and row ${range.rowStart} was updated`
      );

      // const folder = DriveApp.getRootFolder();
      // const file = folder.createFile(imageData.setName("temp-chart-file.png"));

      if (range.columnStart === 2 && range.columnEnd === 2) {
        const scriptProperties = PropertiesService.getScriptProperties();
        const accessToken = scriptProperties.getProperty(
          "MASTODON_ACCESS_TOKEN_SECRET"
        );
        const apiUrl = scriptProperties.getProperty("MASTODON_API_URL");

        const sheet = event.range.getSheet();
        const charts = sheet.getCharts();
        const chart = charts[0];
        const imageData = chart.getBlob();

        const donationsSum = SpreadsheetApp.getActiveSheet()
          .getRange(1, 5)
          .getValue();

        const donationsMedian = SpreadsheetApp.getActiveSheet()
          .getRange(2, 5)
          .getValue();

        const donationsAverage = SpreadsheetApp.getActiveSheet()
          .getRange(3, 5)
          .getValue();

        const USDollar = new Intl.NumberFormat("en-US", {
          style: "currency",
          currency: "USD",
        });

        const donationsSumFormatted = `${USDollar.format(donationsSum)}`;
        const donationsMedianFormatted = `${USDollar.format(donationsMedian)}`;
        const donationsAverageFormatted = `${USDollar.format(
          donationsAverage
        )}`;

        const chartData = chart.getRanges()[0].getValues();

        const dates = chartData
          .map((row) => row[0])
          .filter((val) => String(val).trim().length > 0);

        const dateOptions = {
          weekday: "long",
          year: "numeric",
          month: "long",
          day: "numeric",
        };

        const firstDate = new Date(dates.at(0)).toLocaleDateString(
          undefined,
          dateOptions
        );

        const lastDate = new Date(dates.at(-1)).toLocaleDateString(
          undefined,
          dateOptions
        );

        const description = `A line chart showing daily donations between ${firstDate} and ${lastDate} with ${donationsAverageFormatted} average and ${donationsMedianFormatted} median donations.`;

        console.log(description);

        const mediaUploadResponse = UrlFetchApp.fetch(
          `${apiUrl.replace("/api/v1", "/api/v2")}/media`,
          {
            method: "POST",
            headers: {
              Authorization: "Bearer " + accessToken,
            },
            payload: {
              file: imageData.setName("temp-chart-file.png"),
              description,
            },
            muteHttpExceptions: true,
          }
        );

        console.log("debug:mediaUploadResponse", mediaUploadResponse.getContentText());
        const mediaData = JSON.parse(mediaUploadResponse.getContentText());
        const mediaId = mediaData.id;

        const postStatusResponse = UrlFetchApp.fetch(`${apiUrl}/statuses`, {
          method: "POST",
          headers: {
            Accept: "application/json",
            "Content-Type": "application/json",
            Authorization: "Bearer " + accessToken,
          },
          payload: JSON.stringify({
            status: `The total sum of donations as of today is ${donationsSumFormatted}!`,
            media_ids: [mediaId],
          }),
        });

        console.log("debug:postStatusResponse", postStatusResponse.getContentText());
      } else {
        console.log("skipping...");
      }
    } else {
      console.log("no data was sent");
    }
  } catch (err) {
    console.log(`there was an error: ${err.message}`);
  }
}