Skip to content
This repository has been archived by the owner on Apr 6, 2023. It is now read-only.

Deploy to GoogleSheets

TenteEEEE edited this page Aug 1, 2020 · 2 revisions
  1. Make your spreadsheet
  2. Tool->Script Editor
  3. Write the following code
// Parameter
const csv_url = "https://raw.githubusercontent.com/TenteEEEE/S3Sampler/master/database/song_db.csv";

// Sheet setup
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sh = ss.getSheetByName("RankedSongDB");

// For github webhook
function doPost(data) {
  csv_from_github();
  return;
}

function csv_from_github() {
  const response = UrlFetchApp.fetch(csv_url);
  const content = response.getContentText("UTF-8");
  const csv = Utilities.parseCsv(content);
  sh.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
  add_beatsaver_links();
  return;
}

function add_beatsaver_links() {
  const lastpropety = sh.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
  const propeties = sh.getRange(1, 1, 1, lastpropety - 1).getValues()[0];
  const keycol = propeties.indexOf("Key") + 1;

  const lastkey = sh.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  const keyrange = sh.getRange(2, keycol, lastkey - 1, 1);
  let keys = keyrange.getValues();
  for (let i = 0; i < lastkey - 1; i++) {
    keys[i] = ['=HYPERLINK("https://beatsaver.com/beatmap/' + String(keys[i]).slice(5) + '","' + keys[i] + '")'];
  }
  const writerange = sh.getRange(2, keycol, lastkey - 1, 1);
  writerange.setFormulas(keys);
  Logger.log(keys);
}

If you want to update automatically, please set a trigger.

Clone this wiki locally