How to sum up to a specific value in Google Sheets

Here's an option with formula, that would sort the data (if unsorted) and select the needed registers for the process:

=LET(totalSum,SUM(B2:B),
data,SORT(A2:B,2,0),
lastVal,REDUCE(totalSum,CHOOSECOLS(data,2),LAMBDA(a,v,IF(v<0,a,IF(a<v,a,a-v)))),
cumDiff,SCAN(totalSum,CHOOSECOLS(data,2),LAMBDA(a,v,SUM(a-v))),
position,XMATCH(lastVal,cumDiff),
VSTACK(
  FILTER(data,SEQUENCE(ROWS(data))<position),
  HSTACK(INDEX(data,position,1),lastVal)
))

The output would look like this:

Record 1    97867
Record 2    34653
Record 3    23456
Record 4    12132
Record 5    5778
Record 6    5688
Record 7    5675
Record 8    5456
Record 9    29
Total Sum   194157

To get exactly that display of data, as you shown, you can insert this table inside a formula and BYROW/MAP the registers to get those two columns. In C1 you can use:

=LET(totalSum,SUM(B2:B),
data,SORT(A2:B,2,0),
lastVal,REDUCE(totalSum,CHOOSECOLS(data,2),LAMBDA(a,v,IF(v<0,a,IF(a<v,a,a-v)))),
cumDiff,SCAN(totalSum,CHOOSECOLS(data,2),LAMBDA(a,v,SUM(a-v))),
position,XMATCH(lastVal,cumDiff),
result,VSTACK(
    FILTER(data,SEQUENCE(ROWS(data))<position),
    HSTACK(INDEX(data,position,1),lastVal),
    HSTACK("Total Sum",totalSum) 
  ),
VSTACK(HSTACK("Use?","Value if used"),
BYROW(A2:A,LAMBDA(row,IF(row="","",IF(ISERROR(XMATCH(row,CHOOSECOLS(result,1))),"No",HSTACK("Yes",VLOOKUP(row,result,2,0))))))))

I chose not to fullfill your desires for a spreadsheet layout but here's a way to accomplish what you want in apps script:

Intial Data:

Code:

function gettingSumRecords() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().sort((a, b) => { return (b[1] - a[1]) }).filter(e => e[0] && e[1]);
  const sum = vs.reduce((a, r, i) => {
    a += Number(r[1]);
    return a;
  }, 0);
  const vObj = vs.reduce((a, [rec, v], i) => {
    if ((v + a.rttl) <= a.ts) {
      a.rttl += v;
      let d = a.ts - a.rttl;
      if (i == 0) {
        a.arr.push([rec, v, a.rttl, d])
      } else if (i > 0) {
        let lv = a.arr[a.arr.length - 1][3];
        if (v > 0 && v <= lv) {
          a.arr.push([rec, v, a.rttl, d])
        }
      }
    }
    return a;
  }, { rttl: 0, ts: sum, arr: [], hs:["Used Records","value","Running Total","Difference"] })
  //Logger.log(JSON.stringify(vs).replace(/],/g, '],\n'))
  //Logger.log(sum)
  //Logger.log(JSON.stringify(vObj.arr).replace(/],/g, '],\n'));
  osh.clearContents();
  vObj.arr.unshift(vObj.hs)
  osh.getRange(1,1,vObj.arr.length,vObj.arr[0].length).setValues(vObj.arr);
  const end = "is near";
}

Here's what my output looks like:

You can use Python to automate the process.

1. fetch data from a Google sheet using Google Sheets API
2. process it using DataFrame
3. upload the updated data back to the sheet

Assuming the data is already sorted,

import re
import pandas as pd
import pygsheets

def convert_google_sheet_url(url):
    # Regular expression to match and capture the necessary part of the URL
    pattern = r'https://docs\.google\.com/spreadsheets/d/([a-zA-Z0-9-_]+)(/edit#gid=(\d+)|/edit.*)?'

    # Replace function to construct the new URL for CSV export
    # If gid is present in the URL, it includes it in the export URL, otherwise, it's omitted
    def replacement(m): return f'https://docs.google.com/spreadsheets/d/{m.group(1)}/export?' + (
        f'gid={m.group(3)}&' if m.group(3) else '') + 'format=csv'

    # Replace using regex
    new_url = re.sub(pattern, replacement, url)

    return new_url


def fetch_google_sheet_data(url):
    new_url = convert_google_sheet_url(url)
    return pd.read_csv(new_url)


def process_dataframe(df):
    SUM = df['Value'].sum().astype(int)
    sum_temp = 0
    i = 0

    df['Use Record?'] = 'No'
    df['Value if used'] = 0

    while sum_temp + df['Value'].iloc[i] <= SUM:
        sum_temp += df['Value'].iloc[i]
        df.at[i, 'Use Record?'] = 'Yes'
        df.at[i, 'Value if used'] = df['Value'].iloc[i]
        i += 1

    if sum_temp < SUM:
        df.at[i, 'Use Record?'] = 'Yes'
        df.at[i, 'Value if used'] = SUM - sum_temp

    return df


# this appends the new data to the original worksheet
def update_google_sheet(df, sheet_name, service_file):
    google_client = pygsheets.authorize(service_file=service_file)
    sheet = google_client.open(sheet_name)
    worksheet = sheet[0] # select the first sheet
    worksheet.set_dataframe(df, (1, 1)) # update the google sheet, starting at cell B2


if __name__ == "__main__":
    url = "https://docs.google.com/spreadsheets/d/1bqg5LmP1i2pnfNwhd0ifdISfgfCKzEvqrEK7n5ZNSb0/edit?gid=0#gid=0"
    private_key_path = 'causal-sky-451614-e4-85aa169c3d7c.json'
    df = fetch_google_sheet_data(url)
    df = process_dataframe(df)
    update_google_sheet(df, 'my_spreadsheet', private_key_path)